Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prakriti Agrawal » SARGable
Add to briefcase
30 posts, Page 3 of 3
«««123

SARGable Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Oleg Netchaev
Posted Friday, February 26, 2010 8:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 07, 2013 2:42 PM
Points: 1,662, Visits: 1,710
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
john.arnott
Posted Friday, February 26, 2010 11:02 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010
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
KevinC.
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 07, 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
L' Eomot Inversé
Posted Sunday, February 28, 2010 2:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 7,182, Visits: 7,281
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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware
Post #874252
john.arnott
Posted Sunday, February 28, 2010 11:33 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010
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
Dietmar Weickert
Posted Thursday, March 04, 2010 6:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, January 11, 2013 8:20 AM
Points: 1,608, Visits: 373
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
Paul White
Posted Tuesday, March 30, 2010 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 10,990, Visits: 10,576
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
    Paul White
    Posted Tuesday, March 30, 2010 8:32 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 2:43 PM
    Points: 10,990, Visits: 10,576
    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
    IgorMi
    Posted Thursday, March 22, 2012 11:43 AM
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 3:58 PM
    Points: 1,904, Visits: 1,517
    Oh, I adore questions with fast retrieval of rows. Thank you
    Post #1271091
    L' Eomot Inversé
    Posted Friday, March 23, 2012 7:18 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 4:53 PM
    Points: 7,182, Visits: 7,281
    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
    Is minic a gheibheann béal oscailte dorn dúnta.
    Is minig a cheapas beul fosgailte dòrn dùinte.

    http://es.linkedin.com/in/tomthomsonsoftware
    Post #1271621
    « Prev Topic | Next Topic »

    Add to briefcase
    30 posts, Page 3 of 3
    «««123

    Permissions Expand / Collapse

    		 
     
    Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.