Query tuning - avoid Case statement on this query

Question

Query tuning - avoid Case statement on this query

Viewing 14 posts - 31 through 43 (of 43 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Paul White NZ (10/12/2010)
HowardW (10/12/2010)
I wonder if the confusion arises due to the ISNULL function being non-SARGable?
Quite possibly, but now I have to show you ISNULL being 'SARGed'...

Mind=Blown 😀

Do you have a link that explains how that works? Is it an optimisation specifically for the ISNULL function or can it work with other functions? My understanding was that any scalar function had to be evaluated for each row

Paul White SSC Guru Points: 150468 More actions · Answer 2

HowardW (10/12/2010)
Mind=Blown 😀

Excellent. My work here is done :laugh:

HowardW (10/12/2010)
Do you have a link that explains how that works? Is it an optimisation specifically for the ISNULL function or can it work with other functions? My understanding was that any scalar function had to be evaluated for each row

It's not specific to the ISNULL function, no. It uses the ability of the query optimizer to match expressions from a query to an indexed computed column. It doesn't work with everything - the feature is still relatively new so support is not particularly deep. No doubt it will improve over time.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Ahhhhh, that'll teach me not to read the create table statement properly. Yes, with a indexed computed column it makes perfect sense. I've actually used this trick accidentally when I created an persisted computed column and went to change the code that referenced the function only to find that it was already using it in the execution plan!

Edit: I'd call that cheating. ISNULL is still not SARGable, it's just not using the function!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Paul White NZ (10/12/2010)
Gianluca Sartori (10/12/2010)
Or, like me, question everything he pretends to know! 😛
While I'm messing with your head, how about this:
"LIKE is not SARGable".
It really isn't 🙂

I'm going to try for a brownie point:

If you use LIKE 'SomethingOrOther%' then it's Really Bl00dy Fast

If you use LIKE '%SomethingOrOther' then it's not RBF (which you can defeat by creating an index on REVERSE(SomethingOrOther) and using LIKE 'rehtOrOgnihtemoS%')

CASE works RBF in a filter provided that it's simplistic and clear suggesting to me that it's SARGable if these conditions are met.

IS NULL / IS NOT NULL also work RBF, again suggesting they're SARGable.

But honestly, I don't give a damn if an expression is SARGable according to someone's vanity-press techy-book or not - if it works RBF then I'll use it, and recommend it. Never mind what the book says, what happens when you use it in anger against 75 million rows?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150468 More actions · Answer 5

HowardW (10/12/2010)
Ahhhhh, that'll teach me not to read the create table statement properly. Yes, with a indexed computed column it makes perfect sense.

Good. The dynamic nature of the seek is rather interesting too - look at the details of the Compute Scalar iterator in the plan. It's a very neat trick.

Edit: I'd call that cheating. ISNULL is still not SARGable, it's just not using the function!

You say I cheated, I say I was creative :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 6

Chris Morris-439714 (10/12/2010)
I'm going to try for a brownie point

I'm off to bed now, having contributed somewhat to messing up this thread. Before I go, I'll just explain what I meant about LIKE never being SARGable:

The seek that can result from a LIKE operation (any example) is artificially added by the optimizer. It determines the maximum range of values that could satisfy the LIKE condition, and seeks on those boundary conditions. The LIKE itself is always applied after the range seek as a (non-SARGable) residual predicate. Interestingly, the code that determines the boundary conditions is collation-aware.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Kit G SSCrazy Points: 2888 More actions · Answer 7

Kit G

SSCrazy

Points: 2888

October 12, 2010 at 10:43 am

#1235701

RBF?

-- Kit

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 8

Kit G (10/12/2010)
RBF?

You missed his earlier reference to it: "Really Bl00dy Fast"

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Kit G SSCrazy Points: 2888 More actions · Answer 9

Brandie Tarvin (10/12/2010)
Kit G (10/12/2010)
RBF?
You missed his earlier reference to it: "Really Bl00dy Fast"

Oops. :w00t: Thanks. 🙂

-- Kit

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Paul White NZ (10/12/2010)
I'm off to bed now, having contributed somewhat to messing up this thread. Before I go, I'll just explain what I meant about LIKE never being SARGable:
The seek that can result from a LIKE operation (any example) is artificially added by the optimizer. It determines the maximum range of values that could satisfy the LIKE condition, and seeks on those boundary conditions. The LIKE itself is always applied after the range seek as a (non-SARGable) residual predicate.

Let's see if I can thincken the fog.

It's SARGable if the optimizer can use an index seek to speed up the check. So this one is twice SARGable, since the optimiser uses two index seeks to speed it up twice. SO Like is SARGable in exactly the sense that BETWEEN is SARGable - either both are, or neither is.

Interestingly, the code that determines the boundary conditions is collation-aware.

It had better be. If it wasn't it would often get the boundary consitions wrong!

Tom

hallidayd SSCrazy Points: 2490 More actions · Answer 11

Paul White NZ (10/12/2010)
HowardW (10/12/2010)
I wonder if the confusion arises due to the ISNULL function being non-SARGable?
Quite possibly, but now I have to show you ISNULL being 'SARGed'...
IF OBJECT_ID('Tempdb..#sargTest') IS NOT NULL DROP TABLE #sargTest
CREATE TABLE #sargTest (
number INT NOT NULL PRIMARY KEY CLUSTERED,
indexedColumn INT NULL,
bob AS ISNULL(indexedColumn, 9999)
)
CREATE NONCLUSTERED INDEX IX_SARGTest ON #sargTest (indexedColumn)
CREATE NONCLUSTERED INDEX IX_SARGTest2 ON #sargTest (bob)
INSERT INTO #sargTest
SELECT DISTINCT number, NULLIF(number % 5,0)
FROM master.dbo.spt_values
SELECT number
FROM #sargTest
WHERE ISNULL(indexedColumn, 9999) != PI();
(Dynamic) Index seek on 2005 and 2008.
Paul

Ah - now I recently read a blog post about this trick for mathematical predicates e.g. (contrived example that I think applies)WHERE maNumber * 3 = 6but didn't realise it could be used on functions too.

I remember also Rob Farley submitting a connect request to make some functions sargable since, logically, they could be:

http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

Paul White SSC Guru Points: 150468 More actions · Answer 12

hallidayd (10/12/2010)
I remember also Rob Farley submitting a connect request to make some functions sargable since, logically, they could be: http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

Yes, Rob and I had a very interesting discussion about that just recently, based on one of his webcasts on the subject.

It's very interesting to ask oneself why WHERE col1 LIKE 'A%' is 'SARGable' but WHERE LEFT(col,1) = 'A' is not...:-)

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 13

Tom.Thomson (10/12/2010)
It's SARGable if the optimizer can use an index seek to speed up the check. So this one is twice SARGable, since the optimiser uses two index seeks to speed it up twice. SO Like is SARGable in exactly the sense that BETWEEN is SARGable - either both are, or neither is.

In my mind, the difference is that "a BETWEEN b AND c" is fully transformed to "a >= b AND a <= c" - the original BETWEEN expression does not appear in the plan. The LIKE expression is 'covered' by a suitable range seek, but still evaluated as a residual.

Example: The range of values that satisfy "a LIKE 'B[^b-z]%'" is covered by "a >= 'B' AND a < 'C'", but the full LIKE expression is still performed on the rows qualifying for the 'covering seek'. It may be semantics, but for my money, the covering range is SARGable, but the LIKE is not.

It had better be. If it wasn't it would often get the boundary consitions wrong!

Yes but even if it did get the boundaries wrong, it wouldn't matter since the LIKE would still be evaluated in the residual. The seek would be somewhat less efficient, but it wouldn't be wrong. As it happens, the optimizer is extremely good at converting even quite complex LIKE expressions to collation-aware covering range seeks.

Example: "LIKE 'A[abde]%'" might be covered by "A >= 'Aa'; A < 'AF'

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Paul White NZ (10/12/2010)
Tom.Thomson (10/12/2010)
It had better be. If it wasn't it would often get the boundary consitions wrong!
Yes but even if it did get the boundaries wrong, it wouldn't matter since the LIKE would still be evaluated in the residual. The seek would be somewhat less efficient, but it wouldn't be wrong. As it happens, the optimizer is extremely good at converting even quite complex LIKE expressions to collation-aware covering range seeks.

Obviously that's correct in any case where the way it gets it wrong is that the range to which it restricts is too wide; it doesn't work when the range is neither wide enough nor too wide - if one of the bounds is wrong in the bad (narrowing) direction the final result may also be wrong.

Tom