Re: performance of updates

On Dec 18, 9:53 am, Ed Prochak <edproc..._at_gmail.com> 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: http://jonathanlewis.wordpress.com/statspack-distractions/ http://jonathanlewis.wordpress.com/2007/01/02/superfluous-updates/

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