Re: Insert vs Update

I

On Wed, Jul 15, 2015 at 12:16 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
wrote:

> First off I apologize if this is question has been beaten to death. I've
> looked around for a simple answer and could not find one.
>
> Given a database that will not have it's PKEY or indices modified, is it
> generally faster to INSERT or UPDATE data. And if there is a performance
> difference is it substantial?
>
> I have a situation where I can easily do one or the other to the same
> effect. For example, I have a journaling schema with a limited number of
> "states" for an "entry". Currently each state is it's own table so I just
> insert them as they occur. But I could easily have a single "entry" table
> where the row is updated with column information for states (after the
> entry's initial insertion).
>
> Not a big deal but since it's so easy for me to take either approach I was
> wondering if one was more efficient (for a large DB) than another.
>
> Thanks
>

If you think of an update as a delete-insert operation (glossing over the
fine points of what has to be done for ACID), it seems pretty clear that an
update involves more work than an insert. Measuring that impact on
performance is probably a bit more challenging, because it's going to be
dependent on the specific table and the contents of the row, among other
things.
--
Mike Nolan
nolan(at)tssi(dot)com