Thanks for pointing this out! In my case I have spent much time on normalizing my tables, so the row size should be constant in most cases. I do wonder though, what if the row size is 32 bytes? Or is there a minimum? For instance, I have many lookup tables with ID+text (usually around 20 characters): MyID|MyText With a page size of 4096, will SQLite put ~200 rows in one page?
> Date: Wed, 23 Feb 2011 10:47:03 -0500 > From: pri...@gmail.com > To: t...@djii.com; sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
