Re: performance of updates
Date: Mon, 22 Dec 2008 10:28:33 -0800 (PST)
Message-ID: <671b72d6-faff-473b-9b28-d2a41b0bd6fb@b1g2000yqg.googlegroups.com>
On Dec 20, 12:11 pm, Michael Austin <maus..._at_firstdbasource.com>
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.
Impressive.
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 3. 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