Re: performance of updates

Ed Prochak <edprochak_at_gmail.com> writes:

> I am in the midst of some performance improvements. I have never
> thought about this aspect before but has anyone tested the difference
> in performance between
>
> UPDATE mytable SET
> status = new_status
> WHERE mykey = thiskey;
>
> and
>
> UPDATE mytable SET
> status = new_status
> WHERE mykey = thiskey
> AND status <> new_status;
>
> ???
>
> I haven't run my own test yet. The actual UPDATE is in a trigger but I
> don't think that matters. The argument a coworker is making is that we
> should update only if it makes a difference (ie if the value changed).
> Since this is an update to one row, I'm not sure if the overhead of
> checking additional columns is worth the minimal savings of avoiding
> writing the data.
>
> The difference seems to me like it would be minimal. Anyone have
> evidence either way?
>

There are too many variables to give an answer that would always be true. A lot depends on the table structure, the size of columns, the available keys and indexes and possibly the version of Orcle, the platform its running on and the type of storage.

The only way to really know the right answer is to measure the difference.
Personally, the first thing I'd be asking is whether having a trigger to do the insert is actually the right design. Triggers are something which are often very useful for specific problems, but too often are used when it would be better to do it more explicitly another way - triggers that do inserts and updates are possibly the worst in the sense that your design is now relying on what could be called side effects - often a source of major problems and errors in later maintenance work where the people doing the maintenance are no the ones who designed the system and may not be as familiar with all these side effects.

Tim

-- 
tcross (at) rapttech dot com dot au