Thanks. I understand. I tried to set PRAGMA CACHE_SIZE=0; in the hope of not cache anything and forcing Sqlite to always go to disk - but that didn't help.
I see some reads on pagefile.sys - but both the DB and the pagefile are on SSD - so you would think it shouldn't be too costly to read one or both - even in a more random fashion. The SSD disk read queue is just over 1 - so it doesn't quite fit the typical scenario of IO bottlenecks - a high read queue and a disk arm moving frantically. But who knows. More research I suppose. On Fri, Jul 13, 2012 at 5:58 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca>wrote: > On 13/07/2012 5:37 PM, Udi Karni wrote: > >> Hello, >> >> Running on Windows 7 - I am noticing that tables in :memory: DBs are read >> (SELECTED) at a constant rate. However - conventional DBs on disk - even >> on >> SSD - are read fast the first time, and much slower subsequently. Closing >> and reopening a DB for every SQL statement seems to cure this - but >> obviously is not a solution when you want to run a multi-step SQL script >> on >> a database. >> >> Is this a Windows "feature" - caching or otherwise? Is it Sqlite? Looking >> at perfmon - the initial read "chunk" is > 100K while subsequently it's 4K >> (the page size). Is there some prefetching taking place the first time >> around? How do you make it permanent? How do you make Sqlite consistently >> table-scan from disk? >> > This is a common problem with database buffer caches unless great pains > are taken to avoid it (as in, not a "lite" product). > > What happens is that the first time through the data, it's read > sequentially... but not all of it fits in the page cache. What gets evicted > is *not* sequential, so on the second time around the disk requests are > randomly scattered and take about 100x longer to complete. > > Ryan > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
