Subject Re: [firebird-support] What is better: UPDATE or DELETE + INSERT?
Author Walter R. Ojeda Valiente
Post date 2015-09-16T17:24:12Z
Hello Sean, and everybody

That's a very interesting and very important theme: "the new record version will only include the updated fields -- not a full copy of the row"

Do you know where I can find a document, web-page, book or video that explains that? If it has images or graphics, much better.

Thanks in advance.

Greetings.

Walter.


On Wed, Sep 16, 2015 at 11:40 AM, 'Leyne, Sean' Sean@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
 


> They will generate a comparable amount of garbage, but I think updating the
> existing records is better than delete + insert, especially if there are also
> foreign key references to those records.

I don't think so.

Unless you are updating every column in the row, the UPDATE will generate a smaller disk footprint, as the new record version will only include the updated fields -- not a full copy of the row.

Then there is the impact on index structures that Vlad has pointed out.


From a performance point of view, it would be *much faster*, if you are able to:
- disable all indexes for the target table,
- delete all rows,
- import new rows and
- reactivate all indexes for the target table

This would likely require that you have near-exclusive access/use of the target table.


Sean