On Fri, Feb 25, 2011 at 1:51 AM, Greg Barker <fle...@fletchowns.net> wrote:
> I ran my tests again, this time setting the cache_size based on the > page_size. For some reason I wasn't able to replicate the timing I got > before for the 1024 page size (40.2 seconds) which is really confusing me, > since I was careful to make sure nothing was running in the background > while > I was testing. > > page_size/cache_size: SELECT COUNT time > 1024/2000: 20.83s > 4096/500: 14.4s > 8192/250: 8.8s > 16384/125: 5.0s > 32768/62: 3.6s > > I'm assuming reducing the cache size like that will definitely have an > impact on the performance of an actual application? Optimizing performance > for an application where both the queries and data can take many different > shapes and sizes is beginning to seem like quite a daunting task. What do > you do if there could be anywhere between 30-150 columns? > > Greg, first, I suggest to forget about sqlite cache for scanning operations. This is because the os cache is also used and it's a known fact that they sometimes store the same data twice, so disabling or decreasing one still leaves another in effect. I saw that the db you have is about 400 MB in size. Giving this, if the fastest is 3.6 seconds, you have a very fast harddrive. Can you confirm this? The scanning of such big base with at least 5-years old hd definitely should be 10 seconds or even slower. Did you reboot your comp this time? By the way, a faster way to clear the system cache for a particular file is to "touch" the file with CreateFile(...FILE_FLAG_NO_BUFFERING). I tried to emulate your base with a table CREATE TABLE [TestTable] ([Id] Integer Primary key autoincrement, [a] Text, [b] Text, [c] Text, [d] Text); and filling it with 1,000,000 records having a, b,c,d about 100 bytes each. I saw no big difference between 1024 and 8192 page sizes. When copy the db to Nul took about 20 seconds, 1024-select count took 25 seconds and 8192 - 20 seconds. Anyway, select count usually is a very slow operation regardless of the tweaks we can use, almost in every scenario it's equivalent to reading all the data of the table. I suppose the only exception is if your records is very big to take advantage of bypassing overflow data. So, probably in a table with 100 records each containing a 1MB text, 'select count' will be very fast Max Vlasov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
