SARGable

Question

Post reply

SARGable

Viewing 15 posts - 16 through 29 (of 29 total)

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 1

I think the typo is quite obvious.

Thanks to the author: the question was very useful to me, due to the question I learned the new word 'sargable' 🙂

Seth Lynch SSCrazy Points: 2975 More actions · Answer 2

I got this right because I read the question quickly and didn't notice the missing columns.

However, I have gotten these questions wrong before where there were deliberate 'mistakes' like this.

If this question stood on it's own then it is just a typo. Some questions rely on you spotting or not spotting invalid SQL so the assumption of a typo is dangerous (if losing a point is considered a danger).

Martin_Burton SSCommitted Points: 1931 More actions · Answer 3

no, no, no, i should not have to guess or assume what the question 'should' say.

🙂

Martin

sknox SSChampion Points: 12388 More actions · Answer 4

Dietmar Weickert (2/26/2010)
No info about indices on the table/view to be queried? Who could potentially know about SARGable operators. And even if there were this information, who is able to predict the optimizers way to treat a query.

To answer the first question, a SARGable operation is not necessarily one that is working on an indexed column. A SARGable operation is one that would provide a better result if the column were indexed. So you don't need to know if there is an index to know it's a SARGable operation.

To answer the second question, I'd say anyone whose spent a few hours researching performance tuning in SQL server would have a pretty good idea. That's the whole point of SARGable operations -- they've been identified by people who know how the optimizer works as ones which generally enable the query optimizer to use an index to improve performance. You identify them first, and then you know where indexes would help (or not, as the case may be.)

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 5

The answers have been edited to include the column and "none of the above" has been changed. I will award back points. My apologies as I didn't notice the missing columns.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 6

Tao Klerks (2/26/2010)
Hmm, I was more bothered by the fact that NOT IN() is assumed to NOT be SARGable... In SQL 2000 this was always true when there was more than one value in the NOT IN() parentheses, but as of SQL 2005 the optimizer does a pretty good job of using indexes even then!

At the place where I work we have a weekly Lunch and Learn sessions. About 6 months ago I was giving a presentation about indexes which included a dissection of the SARGable predicates based on the original article by Brad McGehee (the one which is included as a hyperlink in this QoD answer section). Well, when the NOT IN () was investigated when appied to one one the tables in one of our SQL Server 2005 databases, I could not demonstrate that NOT IN () is not SARGable because the execution plan had shown that the respective index was still used. Fortunately, I still had the old SQL Server 2000 database on one of my boxes at home, so I could later confirm the validity of the NOT IN() is not SARGable argument.

On the unrelated note, it looks like occasionally it pays off to come to work late, after Mr. Jones already edited the QoD :-). Since I am very familiar with Brad McGehee's article on SARGable predicates, I did not have any problems answering th QoD. Original typo aside, this is a very good question. Thank you Agrawal.

Oleg

john.arnott SSChampion Points: 11882 More actions · Answer 7

Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.

KevinC. SSCommitted Points: 1570 More actions · Answer 8

I misremembered what SARGable meant and so I got lucky in my answer. A good reminder question.

--

Kevin C.

TomThomson SSC Guru Points: 104773 More actions · Answer 9

john.arnott (2/26/2010)
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.

In my view the use of LEFT should not prevent an index seek and that it does is a bug in the optimiser; after all, col LIKE 'this%' is sargable, so the logically identical LEFT(col,4) = 'this' should also be sargable: having to know which way to write a particular predicate to get the optimiser to notice that an index can be used is really annoying (and a waste of space in every developer's or dba's memory). The current situation is no less stupid than it would be if col < 3 were sargable but 3 > col were not.

I agree it was a fair query - but I looked late and never saw the typo.

Tom

john.arnott SSChampion Points: 11882 More actions · Answer 10

Tom.Thomson (2/28/2010)
john.arnott (2/26/2010)
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.
In my view the use of LEFT should not prevent an index seek and that it does is a bug in the optimiser; after all, col LIKE 'this%' is sargable, so the logically identical LEFT(col,4) = 'this' should also be sargable: having to know which way to write a particular predicate to get the optimiser to notice that an index can be used is really annoying (and a waste of space in every developer's or dba's memory). The current situation is no less stupid than it would be if col < 3 were sargable but 3 > col were not.
I agree it was a fair query - but I looked late and never saw the typo.

Tom, I would tend to agree with your disappointment in the optimizer not recognizing the LEFT(col,4) ='xxxx' construction as equivalent to col LIKE 'xxxx%'. For what it's worth, I just confirmed on a SQL 2005 test db I already had that the estimated plan for a LIKE specifies an index seek where a logically equivalent LEFT() resolves to an index scan. If/Until MS decides to address this, I suppose we'd better continue to pay attention to our predicate constructions at a fairly low level.

------

edit to clarify I'm on SQL 2005.

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 11

Steve Jones - Editor (2/26/2010)
The answers have been edited to include the column and "none of the above" has been changed. I will award back points. My apologies as I didn't notice the missing columns.

Hi Steve, I did not get back my points so far...

Best regards,
Dietmar Weickert.

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

Dietmar Weickert (2/26/2010)
Once again a crap QotD.
A missing column name a typo? Hard to believe.
No info about indices on the table/view to be queried? Who could potentially know about SARGable operators. And even if there were this information, who is able to predict the optimizers way to treat a query.
Just choose whatever you want. It may be right or wrong anyways. Leaves a QotD without value for the community.

Anyone else bugged by a persistent whining noise? :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Bit hash calling the lack of an automatic transformation from LEFT to LIKE a bug!

Are you sure it is a provably safe transformation in absolutely all cases?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 14

Oh, I adore questions with fast retrieval of rows. Thank you

Igor Micev,My blog: www.igormicev.com

TomThomson SSC Guru Points: 104773 More actions · Answer 15

SQL Kiwi (3/30/2010)
Bit hash calling the lack of an automatic transformation from LEFT to LIKE a bug!
Are you sure it is a provably safe transformation in absolutely all cases?

I thought I'd replied long ago, but noticing a new post here I saw I hadn't. better late than never, I suppose.

The optimiser would have to look at each case and decide whether the transformation was allowable; and if it was allowable, is it worth doing in the particular case (the latter is the same as for any other choice the optimiser makes, of course).

The transformation is from LEFT(Col,L)=S to Col LIKE tr(left(S,L))+'%', where the expression tr(left(S,L))+'%' is calculated once only (by the optimiser); tr is the string escaping needed to deal with wildcards occurring within left(S,L) - they must not be treated as wildcards because they represent themselves; the transformation is permissable provided that (i) left(S,L) does not have a space as its final character and (ii) S and L are constants known to the optimiser, and will be the same each time the generated plan is used. If any of these conditions fails, the optimiser must not male the transformation.

Of course there are many other "bugs" that probably have higher priority than this ("bug" of course means "possibility for improvement in teh optimiser", not anything else).

Tom