Post reply

Hello i found somethings when i was playing with the execution plan

  • March 22, 2005 at 9:42 am

    #65619

    Hello my friends, you know, when i was playing wiht the execution plan of the

    query analizer i found that my queries got a little best perfomance

    when i used 'patindex'  instead of 'like'

    and when i used '>'  instead of '<>'

    are right my thoughts?

    is there some friend that knows the because of the why the 'patindex' is better than 'like '

    and the '>' is better than '<>' ??????  

     

    sorry my bad english....

  • March 22, 2005 at 10:01 am

    #548236

    #1  PATINDEX may give better performance because it is optimized for MS SQL and not just generically created SQL 92 statement LIKE.  (Probably wrong on this ....)

    #2 > is a better performer than <> strictly because > is ONLY looking for values ABOVE the one specified.  <> looks BOTH ABOVE and BELOW to determine equality



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • March 22, 2005 at 11:54 am

    #548275

    every day one learnt a new thigs thanks my friend

    one question are you a girl?

  • March 23, 2005 at 10:02 am

    #548538

    Use LIKE when comparing from the beginning of a string.

    Otherwise, LIKE and PATINDEX will usually have the same execution plan.

    P

  • March 23, 2005 at 10:40 am

    #548549

    If I remember correctly "<>" is equivalent to "NOT =" in some queries and anytime you use "NOT" the optimizer tosses out many useful (efficient) query plans auto-magically. This "NOT" behaviour is not just limited to SQL Server ... Sybase and DB2 are also in the same boat.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 23, 2005 at 11:02 am

    #548557

    I couldn't find a ready reference in BOL, but IIRC, LIKE forces a table walk and I think <> also does.  I'm sure it's on the MS site somewhere.  Definite performance hit on big tables.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • March 23, 2005 at 12:14 pm

    #548580

    I tried LIKE and PATINDEX on a table with a unique constraint. When searching at the beginning of the string ('C%'), both used that index, with PATINDEX using INDEX SCAN and LIKE using INDEX SEEK (optimal).

    When searching anywhere in the string ('%C%'), they both used that index, and both did an INDEX SCAN. This was surprising, I was expecting TABLE SCAN in both cases, but there you have it...

    But both LIKE and PATINDEX most certainly do not force table scans in all cases.

    P

  • March 23, 2005 at 3:03 pm

    #548613

    Hmmm.  Maybe it was specific to a previous version of SQL.  I know I read it, the question is where and when.

    Thanks for the info!  I'll definitely have to apply it and watch the results.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • March 23, 2005 at 5:27 pm

    #548636

    Note that, on a table with a clustered index, you'll never do a table scan. If you're using the index, you'll do a clustered index seek; if not, a clustered index scan (since the clustered index is where all the data is anyway).


    R David Francis

  • March 24, 2005 at 7:38 am

    #548741

    Non-clustered index seek on SQL 2000 sp2. The table does not have a clustered index.

    P

  • March 24, 2005 at 8:03 am

    #548757

    Quote Hector 

    every day one learnt a new thigs thanks my friend

    one question are you a girl?

    Sorry, no insult intended Arthur - but I found this really funny.....

    Hector - top tips, check Arthurs  website - you will learn something new again today.......

    http://www.kritter.net/AJ_Resume.doc may give you a pointer to the answer to your question

    Ask a silly question..

    Have fun - youv'e made my day much more amusing......

    Steve

    We need men who can dream of things that never were.

  • March 24, 2005 at 8:17 am

    #548762

    sorry i said that because i saw a picture of girl in the profile, sorry that was a silly question. sorry Sr Arthur

     

  • March 24, 2005 at 9:18 am

    #548791

    As I said, no intention of insulting or irritating anyone - it just made me laugh (but I have got a strange sense of humour.....). It gave me a good start to a long weekend off.

    Have fun

    Steve

    We need men who can dream of things that never were.

  • March 24, 2005 at 5:26 pm

    #548888

    Steve,

    No offense taken.  That wouldn't be the first (nor the last probably) that I have been called a girl .  I just happen to be a PROUD papa ... If you navigate around the one with a goatee is me

    Have a GREAT weekend and Happy Easter



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 14 posts - 1 through 13 (of 13 total)

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