On Wed, Feb 23, 2011 at 11:12 AM, Sven L <larvpo...@hotmail.se> wrote: > > 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?
Yes, very roughly. There is other internal information: a header on each page, on each row, on each field, ints are variable length, etc., and SQLite reserves some free space on each page for later inserts. Use sqlite3_analyzer for lots of useful info when picking a page size. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com > > > >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
