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? I'll post the times I got before, when using the default cache_size of 2000: 1024: 40.2s 4096: 15.5s 8192: 8.5s 16384: 5.3s 32768: 3.8s On Thu, Feb 24, 2011 at 11:19 AM, Greg Barker <fle...@fletchowns.net> wrote: > Average payload per entry for my test table was 409.00. Sounds about right > since the db has 4 columns and each is filled with a random string of length > 100. > > I've uploaded the full output from the sqlite3_analyzer for a few different > page sizes: > > http://fletchowns.net/files/1024-analyzer.txt > http://fletchowns.net/files/4096-analyzer.txt > http://fletchowns.net/files/8192-analyzer.txt > > I'm not quite sure how to interpret everything in there. What stands out to > you guys? What is Average Fanout? > > Greg > > > On Thu, Feb 24, 2011 at 4:28 AM, Max Vlasov <max.vla...@gmail.com> wrote: > >> Yes, Greg, please post this value for this table you use in count query >> >> Max >> >> >> On Wed, Feb 23, 2011 at 9:58 PM, Greg Barker <fle...@fletchowns.net> >> wrote: >> >> > Is the record size you refer to here the same as the "Average payload >> per >> > entry" that sqlite3_analyzer determines for me? >> > >> > On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov <max.vla...@gmail.com> >> wrote: >> > >> > > 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. >> > > >> > > Max >> > >> > >> _______________________________________________ >> 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
