To paraphase Forrect Gump, reasonable is as reasonable does. It's computed by cachesize*pagesize
Pagesize is limited to 65536 but I don't know what cachesize is lmited to (docs don't say). You can make it abosolutely huge if you have the memory for it. And 40MB sounds awfully small to me. Try cachesize=100000 and see what happens. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ian Hardingham [i...@omroth.com] Sent: Sunday, March 13, 2011 8:40 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Optimising a query with several criteria Hi Michael, thanks for this. My database is 40 megabytes (and growing slowly) - is that a reasonable cachesize? On 13/03/2011 13:07, Black, Michael (IS) wrote: > You don't say how big your database is. > > My guess is when you see the server using a lot of RAM (and exactly how are > you measuring this?) that it's flushing its disk cache. If you're on Unix > use vmstat to see what your OS cache is doing. > > So...perhaps if you increase SQLite's internal cache it might help. > > pragma cachesize=2000 is the default with a default pagesize of 1024 I think > so it's 2MB by default. > > Make your cache as big as your database is and see what happens. That way > the OS will be forced into swap if it needs more RAM and the low-priority > items will get swapped out instead of you losing disk cache. > > > Michael D. Black > Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > ________________________________________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Ian Hardingham [i...@omroth.com] > Sent: Sunday, March 13, 2011 6:43 AM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] Optimising a query with several criteria > > Hey guys. > > I've optimised most of my queries to work effectively, but I have one > which is sometimes causing me problems. It is: > > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > multiturnTable has about 100,000 rows. > > (My apologies if I keep harping on about this same general area). > > This query needs to run only once per client session - which isn't very > often. However, when the server is taking up a lot of RAM I've seen > this query take 30 seconds. When there's plenty of RAM it only takes in > the region of 100ms. Does anyone have any advice? > > Thanks, > Ian > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
