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. > >
