Re: performance of updates

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.

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.

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

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. Received on Sat Dec 20 2008 - 11:11:19 CST