RE: Help needed in Performance Tuning

SSC Guru

Points: 1003853

January 26, 2015 at 10:43 pm

ScottPletcher (1/26/2015)
dwain.c (1/25/2015)
coalesce(a.processStatus, 0) = 0
is not SARGable, so that could be improved on by making the processStatus column NOT NULL.
It's much better to code it as:
(a.processStatus is null or a.processStatus = 0)
When an index is available, SQL can still do a seek for the code above.
In short, the rule is:
NEVER use ISNULL/COALESCE in a WHERE or JOIN clause; you can always code around it.

"Never say Never". 😛 There are places in the WHERE and JOIN clauses where this works a treat. For example, it works just fine on variables and, under just the right conditions, it can be used on column names and you can still get seeks out of it. You just have to make sure that it's not the only column name and that it's buried under 2 or 3 other column names in the same table that have a composite index on them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)