Jay Sprenkle wrote:
On 9/13/05, GreatNews <[EMAIL PROTECTED]> wrote:
Hi D. Richard Hipp,
I'm developing a desktop rss reader using your excellent sqlite engine.
One
issue my users found is that sqlite database can get heavily fragmented
over
time. I'm wondering if it's a viable suggestion that sqlite pre-allocates
disk space when creating database, and grows the db file by bigger
chunk(e.g. grow by 20% or so in size each time)?
Why not do a vacuum every 10th time (or something similar) you exit the
program?
---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
Even vacuuming won't defrag the file. Disk space is allocated by the OS
and the OS makes no guarantees.
If the program is running on Windows, then you can defrag the file your
self (if you have admin rights). You can read about the Win32 defrag
APIs here http://www.sysinternals.com/Information/DiskDefragmenting.html.
We don't defrag the database file, but I do vacuum it whenever the slack
space exceeds 25% of the total file space used by the database file. We
check for this when our application starts up.
I ported a hack from sqlite2 to sqlite3 that calculates the amount of
slack space. I submitted it (probably improperly) for inclusion into
sqlite a few weeks ago. I can provide it to you if you wish, just email me.
You could do the following:
1) Create a dummy table and fill it with a gazillion [1] rows of junk.
2) Defrag the database file.
3) Drop the dummy table.
4) You just created lots of slack space that will be reused by sqlite
before sqlite extends the disk file (I think).
[1] for suitable values of "a gazillion".
