You're right Charles, in a case where most of the rows will be updated
anyway, COALESCE might perform better.
Furthermore, if the data in tableA is truly 'bad', then there is no choice
but update all of the rows.
However, from what I've seen in practice, there are often triggers that have
to be fired and journal entries that have to be deposited on the table being
updated. Journal entries then may generate secondary processing for a
'journal reader' program. These costs may overwhelm any small benefit of an
'update all' approach.
That said, the 'update all' processing is usually done for one-time
processing, to fix bad data and such. So perhaps the difference in approach
isn't important anyway.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Re: Newbie SQL problem with UPDATE - SQL0811
I normally don't argue with Elvis about SQL performance but....
If most rows have a match, wouldn't COALESCE perform better?
On the other hand, if most rows don't have a match, I could see EXISTS
performing better as long as the right index exists.
Charles
On Fri, Apr 10, 2009 at 3:16 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
BTW, for performance reasons, stick with the EXISTS and forget the
COALESCE
in this case.
midrange.com
