Re: performance of updates

From: Charles Hooper <>
Date: Thu, 18 Dec 2008 07:38:15 -0800 (PST)
Message-ID: <>

On Dec 18, 9:53 am, Ed Prochak <> wrote:
> 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?
>   Ed

What seems like a small improvement now may become a larger problem later as the decision not to limit the rows to be updated is reused in other applications. You might want to review the following:

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Dec 18 2008 - 09:38:15 CST

Original text of this message