Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SARGable Expand / Collapse
Author
Message
Posted Friday, February 26, 2010 8:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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

Post #873504
Posted Friday, February 26, 2010 11:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.
Post #873599
Posted Friday, February 26, 2010 11:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
I misremembered what SARGable meant and so I got lucky in my answer. A good reminder question.

--
Kevin C.
Post #873614
Posted Sunday, February 28, 2010 2:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:11 PM
Points: 7,920, Visits: 9,646
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
Post #874252
Posted Sunday, February 28, 2010 11:33 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.
Post #874314
Posted Thursday, March 4, 2010 6:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, Visits: 374
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.
Post #876771
Posted Tuesday, March 30, 2010 8:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
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?




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #892834
    Posted Tuesday, March 30, 2010 8:32 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Today @ 5:25 AM
    Points: 9,928, Visits: 11,204
    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
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #892836
    Posted Thursday, March 22, 2012 11:43 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Wednesday, December 10, 2014 12:24 PM
    Points: 3,098, Visits: 3,231
    Oh, I adore questions with fast retrieval of rows. Thank you



    Igor Micev,
    SQL Server developer at Seavus
    www.seavus.com
    Post #1271091
    Posted Friday, March 23, 2012 7:18 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 7:11 PM
    Points: 7,920, Visits: 9,646
    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
    Post #1271621
    « Prev Topic | Next Topic »

    Add to briefcase «««123

    Permissions Expand / Collapse