Thanks. Here is something that seems to work - I open a Sqlite "scratchpad" DB into which I select subsets from master tables - which are the problem because they are large.
Initially I put each master table in its own DB and attached them all in the beginning of the script. The first read was fast and the subsequent ones slow. I now attach the master DBs I need for a given SELECT right before the SELECT and then detach them. I repeat this on every SELECT statement where I refer to the large external tables. Definitely not pretty - but it works ! The master tables are now read fast every time. Somehow this is faking Windows to think it's the "first time" every time. Sigh... So this is bad - ========== attach TABLE1... SELECT TABLE1 (fast) SELECT TABLE1 (slow) SELECT TABLE1 (slow) detach TABLE1 And this is good - ============ attach TABLE1... SELECT TABLE1 (fast) detach TABLE1 attach TABLE1... SELECT TABLE1 (fast) detach TABLE1 attach TABLE1... SELECT TABLE1 (fast) detach TABLE1 Whatever works... On Fri, Jul 13, 2012 at 7:11 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 14 Jul 2012, at 3:07am, Udi Karni <uka...@gmail.com> wrote: > > > 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. > > I don't think you can alter this inside SQLite. The caching you have > noticed is not being done by SQLite. It's either a part of Windows' > read-ahead caching, or something being done by your storage driver. > > Windows gets a lot of its speed by doing things like read-head caching. > It can lead to strange results like the ones you reported. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > 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
