Re: [sqlite] Slow query after reboot

[Geoff Simonds]

Thanks to everyone for all the help on this problem.  I am going to try 
creating a new thread to touch the tables at startup. 

Chris Schirlinger wrote:

We have the same issue, to get around it we fire a thread when the 
program starts, intelligently "touching" every table that the user is 
likely to access (As Michael Sizaki already mentioned a select 
count(last_column) from big_table; will do it)

Since a user is very unlikely to run a program and start doing 
anything complex within the first 10 to 20 seconds, by the time they 
DO try and run a query, it is the "second time" the query has been 
run and this we get the benefit of the Windows XP cache and thus the 
fast speed

 

I have created a client application that is always running on a users
desktop. The application accepts user input and then uses SQLite to
perform a few simple queries against a single db file that contains 4
tables. The performance is fantastic after the initial install and
normal usage. When the user leaves for the night and tries a query in
the morning, the app hangs for 20 seconds and then finally comes back
with the results. If the user then duplicates the query immediately
afterward, the query is almost instantaneous. In addition, if at any
point the user reboots the machine and then retries the query, the same
delay happens.  The time is spent in the SQLiteDataReader.read()
method.  Does anybody have any thoughts on why this is  happening?
Thanks for any help.