The SQLite cache size is in pages (2000 by default), so by increasing the page size 8x, you're also increasing the cache size and memory requirements by 8x. Not saying it's a bad thing, just something to be aware of.
If you want to compare 1K and 8K page size and only compare the effect page size has, you should either increase the cache size to 16000 for 1K pages or decrease the cache to 250 for 8K pages. The other thing to be aware of is that SQLite will not allow a row to cross 2 pages. (It does allow a row to be larger than a page, using an overflow page.) So for example, if your page size is 1024 and row size is 512 bytes, you can fit 2 rows on a page. I'm simplifying this somewhat and ignoring internal SQLite data, but you get the idea. If your row size is 513 bytes, you will have 511 bytes of waste on each page, so 50% of your database will be "air". As your row size heads toward 1024 there will be less waste. At 1025 bytes, SQLite will start splitting rows into overflow pages, putting 1024 bytes into the overflow page and 1 byte in the btree page. These numbers aren't right, but illustrate the point. So to find a good page size, experiment and measure. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Feb 23, 2011 at 10:20 AM, Teg <t...@djii.com> wrote: > Hello Greg, > > I found this to be the case too. The difference between 1K and 8K is > staggering. I default all my windows DB's to 8K now. > > > Tuesday, February 22, 2011, 1:59:29 PM, you wrote: > > GB> I'm currently dealing with a similar issue. I've found that the page_size > GB> PRAGMA setting can have a dramatic effect on how long it takes to "warm > up" > GB> the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) > GB> takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 > takes > GB> 8.5 seconds. This was done with a reboot between each test. > > > > > -- > Best regards, > Teg mailto:t...@djii.com > > _______________________________________________ > 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
