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
