I have a sqlite database that I am running a query on and there is a select statement that is working much slower than I thought it would.
I have one db method for getting a single table
def get_tables_by_id(self, id)
with self.conn as conn:
c = conn.cursor()
c.execute(SELECT * FROM tbl WHERE foreign_id = ? AND date(date) >= ? AND date(date) <= ?, params)
return c.fetchall()
I then have another method to do this many times under different parameters. Instead of preparing a single statement to fetch them all at once I thought I would just loop and call get_tables_by_id because I am not hitting the database over the network and I would have to do some operations to organize everything if I queried them all at once. I thought since sqlite runs on the filesystem it would be fast... but it slows down after 20 or so calls to the above method. (I am trying to call it 1000 times)
If I however execute the following SQL and run it in one big query, it returns all of the results instantly...
q = f"SELECT * FROM tbl WHERE foreign_id IN ({','.join('?' * len(foreign_ids)}) AND date(date) >= ? and date(date) <= ?"
I would understand if the looping query took longer, on the order of seconds, but I guess it would take 5 minutes to complete at the rate that it was going at. WHat would be the reason for such a slow down like that?
