On 10/6/2010 10:55 AM, Fulvio Senore wrote:

> I am curious about a simple question. If I understand it correctly,
> each time that I update a row the database keeps a copy of the original
> version. When I commit, the copy becomes garbage ready for a sweep.

Close, but not quite. The copy becomes garbage when the last
transaction that ran concurrently with your transaction finishes.
If you want to be even more specific, when the last concurrent
transaction that is not running in Read-Only, Read-Committed mode
finishes. And the garbage sits around waiting for some other
transaction (or the garbage collect thread) to run across it and
remove it. Unlike PostgreSQL, Firebird has continuous and
cooperative cleaning.

>
> So, updating a row creates some garbage. If I delete the old row and
> then I insert the new one this also creates some garbage, of course.

When you update a row, you create one back version of the record. If
the changes are not major, that is a difference record - just enough
to apply the changes and recreate the old version. When you delete
a record, you create a "deleted stub" - basically just a record header,
and the entire old record becomes the back version.

>
> The question is: does updating a row create significantly less garbage
> than deleting the row and inserting it again?

Yes, updating a row will create less garbage than deleting and
reinserting the record. A delete and reinsert will results in
a primary record version with all the data, a deleted stub, and
a back version with all the data again. When you update a record,
if the changes are so significant that the information necessary
to recreate the old record version from the new one are larger
than the record, you'll have two full copies of the record, still
less than two full copies plus a deleted stub.

>
> If I update a row with the same values (no change) does this cause
> garbage too, or the engine is smart enough to notice that there is no
> change?

I think you get a zero length difference record - with a record header
of course. It's important to create a new version because even a no
change update gives your transaction "ownership" of the newest copy
of the record.

>
> I ask this because there are situations (like the items of an order,
> when the user updates the order) when it would be much simpler to delete
> all the old rows and then insert all the new ones (the number of the new
> rows might be different from the number of the old ones). I would like
> to know how inefficient would be such a practice for the Firebird
> database architecture.

Not terribly. It can wreck havoc with foreign keys, but if that's
not a problem for you, a complete update is about 13 bytes less than
a delete and reinsert.


Good luck,


Ann