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
