Re: performance of updates

From: Ed Prochak <>
Date: Mon, 22 Dec 2008 10:28:33 -0800 (PST)
Message-ID: <>

On Dec 20, 12:11 pm, Michael Austin <> wrote:
> 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
>  From the OP Given:
> 1) Some change is made to table1
> 2) A trigger is evaluated - not sure if it is an insert or delete update
> trigger - this can make a difference.
> Actually the UPDATE in a trigger CAN matter.  If mykey is properly
> indexed and the number of rows where mykey=thiskey is significant-
> compared to the overall data, then adding a second "status" <>
> thisstatus MAY be faster especially if you have a compound mythis,status
> index.  If mykey is somewhat or completely unique AND is properly
> indexed then adding status would not impact performance.

mykey is indeed unique, my issue should not be improved with the additional condition (but not hurt either). This looks like what I am seeing so far.
> I have had databases (not my design - I was called in to fix
> performance) where the trigger on one table would cause IIRC 11 other
> table triggers to evaluate/execute.  Because they did not do the initial
> trigger properly, this caused severe performance when trying to load
> thousands of records/sec. By ensuring the proper "trigger" was in place,
> (in this case - don't update status unless status actually changed to
> 'somevalue' as was intended) I was able to get them about a 50% boost in
> performance.

 In my case if there was a trigger chain, I would be at the tail end of it.

> The KEY is for the DBA to know and understand the data and the logical
> view of the database.

Separate story. I won't start into that now.

> I know that over the past 5 years RAD (rapid application development)
> has significantly decreased the ability of the development staff
> (programmers, DBA, Sysadmin, SANadmin, and Network admins) to have any
> understanding of applications and how they should function.  Once
> deployed, they then spend most of their waking hours trying to achieve
> performance that can only be gained by designing performance into the
> app.  No amount of physical tuning will ever overcome a poorly designed
> (or should I say thrown together) application and database.  I was
> recently asked to review a database that was developed on a PC under
> someones desk with Access and quickly converted to single CPU SQL Server
> and deployed to support >10K users/min - and they were simply amazed
> that it didn't work.

Well, I'll repeat my performance mantra here: the first three steps to improving performance in Oracle are:

1. analyze
2. Analyze

The fact that after running an analyze in the test database recently broke lots of things, may give you a hint at my situation.

Thanks ALL!

   Ed Received on Mon Dec 22 2008 - 12:28:33 CST

Original text of this message