On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker <fle...@fletchowns.net> wrote:
> I'm currently dealing with a similar issue. I've found that the page_size > PRAGMA setting can have a dramatic effect on how long it takes to "warm up" > the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) > takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes > 8.5 seconds. This was done with a reboot between each test. > > This page recommends a page_size of 4096: > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows > > If I see better performance with the larger page sizes (going to test 16384 > and beyond after this) is there any reason not to use them? > > Greg, you should also take the record size into account. My hypothesis is that if your record is comparatively small (several fits into 1024) the speed of select count will be the same for any page size (my quick tests confirm this). It's interesting to know what is an average size of your record to understand why the numbers are so different. Returning to the original topic, for performance reasons I sometimes recommend using an index created on the id/rowid. It's a strange construct that makes no sense, but actually it sometimes give a speed improvement. This is because any index contains only the data used in it and if the query doesn't require getting additional data from the table it was created for, sqlite only reads this index and nothing else. So to get the fastest count result one can create the following index (assuming id is the alias for rowid) CREATE INDEX [idx_MyTableId] ON [MyTable] ([ID] ) And use the following query SELECT COUNT(id) from (SELECT id FROM MyTable ORDER By Id) "Order by" here forces using this index and I used outer select since count(id) inside the main select for unknown reasons triggers the table scanning. For any query in my tests that usually takes 5-50 seconds, this one is always less than a second. But is costs a little in term of the size (the index takes space) and the speed of insert. If this is a small price to pay then this may be an answer. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
