TOPIC: Database size grows when I modify records...
Database size grows when I modify records... 3 Years, 3 Months ago
When I modify some records in a Suneido table, the size of the suneido.db file grows in the same way as when I add new records... This means that when I modify a record, the unmodded record is preserved in the suneido.db?
If this is the case, how can I prevent this behaviour? If the data aren't only text, but there are, for example, images too, and an user modify often a record, then the database grows very quickly.
A solution could be 'compacting' the database before running Suneido. A batch file is the better solution for this?
Re:Database size grows when I modify records... 3 Years, 3 Months ago
Yes, updated versions of records are added to the end of the database in a similar way to new records.

There are several reasons for this. One is crash recovery - the database itself serves as a sort of "log file" that allows recovering up to a certain point. If you updated records "in place" this would not be possible. Another reason is for concurrency - since transactions "see" records as of their start time, you need to be able to access old versions of records. Although in this case you only need the old versions for a limited amount of time - until concurrent transactions are finished.

This is also what allows the "history" feature - which uses the old versions of records still in the database. Which is why the history only works until you compact the database.

The only way to recover this space is to compact the database which currently requires stopping and restarting the server. For our customers we set up a weekly scheduled task that stops the server, compacts the database, and then restarts the server.

Sometimes you can reduce the problem by separating the frequently changing fields into a separate table from the more stable fields, especially when the stable fields are the big ones. This requires using a 1:1 join to combine the two tables, but this kind of join is update-able so it can be used directly in an Access.

In practice, the wasted space is not much of a problem. It does not slow down the database much and most people have plenty of disk space.

Sometime I would like to add background compacting to Suneido. I got some ideas about this from an article in Dr. Dobbs on the Java Edition of the Berkeley DB

Post edited by: andrew, at: 2007/05/03 21:31