Database size (my old story)

ALL

Database size (my old story)

Postby ALL » Tue Jul 13, 2010 5:01 am

Hi Alex

I say my old words about size of database in SQLite
version: 1.2p1
file: http://rapidshare.com/files/406649732/cintanotes.db

1) I add some notes
2) I set tags for its
3) I RENAME tag 444 to other name,
4) I rename other name to 444
5) I do it several times

See attachment file:
If you EXPORT TO XML this file, after this DELETE database.file, open CN & IMPORT XML, filesize will be 26kB. Now it is 40 kb

Maybe todo features - 'optimize database size': auto-export in XML, delete, auto-import XML

But, It isn't critical. I think
Best regards.
Guest

Re: Database size (my old story)

Postby Guest » Thu Jul 15, 2010 5:20 am

additions:
0) create 30 notes with tag '444', see file-size of database
1) I rename 3-chars tag 444 to 10-chars tag 1234567890
2) Close CN
3) Open CN and rename its tag to 444
4) Close CN
size of databe will be more than in 0) step
User avatar
CintaNotes Developer
Site Admin
Posts: 4689
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: Database size (my old story)

Postby CintaNotes Developer » Tue Jul 20, 2010 10:14 am

Hi,

well CN now uses the AUTO VACUUM feature of SQLite, which is not as effective as executing the VACUUM command manually.
(You can do it by hand via downloading the sqlite3.exe command interpreter and issuing the following command: "sqlite3 cintanotes.db vacuum").

Here is what SQLite docs say:

When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse.


This is the everlasting issue of balance between speed and size.

But you are right that users who care a lot about size should have the option to trade some speed for size.

I've added the idea to the roadmap:
http://roadmap.cintanotes.com/feedback/ ... y-on-exit/
Alex
ALL

Re: Database size (my old story)

Postby ALL » Wed Jul 21, 2010 4:10 am

Hi
OK. thanks.

Return to “Bug Reports”