Zbigniew Baniewski <zb-pij/[EMAIL PROTECTED]> wrote:
On Mon, Jan 21, 2008 at 09:59:08AM -0500, Igor Tandetnik wrote:
In the paper above I've found a ready-to-use recipe, which I'm using
since, because is very practical. First I'm copying the data from
"physical file" into "memory database", then I'm operating in memory
- and, at the end of work, all the memory contents is written back
into the file.
So, I'm deleting the file contents first:
SELECT name FROM sqlite_master WHERE type='table'
DELETE FROM $name
...then attaching "physical" file...
ATTACH /full/path/to/dbase AS app
...and then putting the "memory database" contents into file:
SELECT name FROM sqlite_master WHERE type='table'
INSERT INTO app.$name SELECT * FROM $name
...detaching it in the end: DETACH app
So, back to my question: do I need VACUUM at all?
You may need to (unless you are happy with the database file never
getting smaller). Suppose your work on the memory database involved
mainly deleting records. The amount of data you put back into the file
will then be less than the amount of data it used to hold at the
beginning of the operation. This would necessarily mean there are some
free pages in the file, and the file is larger than it strictly needs to
be. VACUUM will reclaim free pages and reduce the size of the file.
Now, if you expect the amount of data to grow in the future, it is fine
to leave free pages in. They will be reused as necessary. In other
words, you don't need to VACUUM if you are happy with your file
retaining the high water mark size (the size reflecting the largest
amount of data it ever held).
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
