Help needed in Performance Tuning

Question

Help needed in Performance Tuning

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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)

ScottPletcher SSC Guru Points: 101103 More actions · Answer 2

Jeff Moden (1/26/2015)
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.

In this case, I would say NEVER. There's no need to use it. It's my understanding that SQL will never do a true seek on columns embedded in a function. Of course other conditions in the WHERE might use indexes if they aren't embedded in functions. But it would still be better to provide the SQL engine all search values in a sargable format so that the most accurate lookup is possible.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

ScottPletcher (1/27/2015)
Jeff Moden (1/26/2015)
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.
In this case, I would say NEVER. There's no need to use it. It's my understanding that SQL will never do a true seek on columns embedded in a function. Of course other conditions in the WHERE might use indexes if they aren't embedded in functions. But it would still be better to provide the SQL engine all search values in a sargable format so that the most accurate lookup is possible.

Guess I'll have to prove it then. 🙂 I'll be back.

--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)