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
