Which one is faster?

  • Emailaddress have non-cluster index. I read first statement will use table scan and second one will do index seek which is faster then first statement. but when I do my self it both show table scan in execution plan.

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca'

    or

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%'

  • The second can do an index seek, but probably the index is not covering and the query returns too many rows for the seek + key lookups to be efficient.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The second query can do a scan or seek (most probably a scan) depending on indexes.

    What are the index definitions you have in place?

    What is the selectability of the value 'ca'?

    If you must only search for the domain of an email address (or just email parts), have you considered expanding the table definition to include a computed column for instance? Or maybe an indexed view?

    But if you have sufficient records, both queries will produce a scan in either case.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I used, following index.

    CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber

    ON omar.dbo.a123 (EmailAddress)

  • That index is not covering for your query. And since an emailaddress is more than just the two characters (or three since you are probably searching for com, org, ca, etc), you should consider revising the query or the table design.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Can you also post the DDL of the table. If the table has a clustered index on firstname then that value is automatically included in non-clustered indexes and thus is considered covering and can be used by the query engine if it decides it meets criteria.

  • create table omar.dbo.a123

    (

    contactid char(10),

    firstname varchar(20),

    emailaddress varchar(20)

    )

    CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber

    ON omar.dbo.a123 (EmailAddress)

    Let's assume, there is million of record, all I am trying is to see, seek index in second sql statement from following:

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca'

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%'

  • Munabhai (3/28/2012)


    create table omar.dbo.a123

    (

    contactid char(10),

    firstname varchar(20),

    emailaddress varchar(20)

    )

    CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber

    ON omar.dbo.a123 (EmailAddress)

    Let's assume, there is million of record, all I am trying is to see, seek index in second sql statement from following:

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca'

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%'

    If your table has a million records, and both queries are still doing a table scan, SQL thinks that scanning the entire table will be better than seeks + bookmark lookups using the NC index.

  • Munabhai (3/28/2012)


    create table omar.dbo.a123

    (

    contactid char(10),

    firstname varchar(20),

    emailaddress varchar(20)

    )

    CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber

    ON omar.dbo.a123 (EmailAddress)

    Let's assume, there is million of record, all I am trying is to see, seek index in second sql statement from following:

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca'

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%'

    You should only see a table scan. If you remove FirstName from the queries then you should see a seek in the second case. In this situation the queries should have roughly the same performance.

  • Thanks Lynn Pettis,

    I do like your answer, if query optimizer table scan is best rather then index seek.

  • Munabhai (3/28/2012)


    create table omar.dbo.a123

    (

    contactid char(10),

    firstname varchar(20),

    emailaddress varchar(20)

    )

    CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber

    ON omar.dbo.a123 (EmailAddress)

    Let's assume, there is million of record, all I am trying is to see, seek index in second sql statement from following:

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca'

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%'

    Ummm... do you have a clustered PK or unique clustered index anywhere on that table???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No I donot have any cluster or pirmary or unique key in table, but as Antares686 said

    select EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%' it says index seek but I donot know why it is saying index seek once I remove first name.

    But I donot know why different scan and seek.

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca' -- does table scan

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%' -- table scan

    --removing firstname

    select EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%' -- Index seek

    select EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca' --Index Scan

  • Munabhai (3/28/2012)


    No I donot have any cluster or pirmary or unique key in table, but as Antares686 said

    select EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%' it says index seek but I donot know why it is saying index seek once I remove first name.

    But I donot know why different scan and seek.

    select FirstName, EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca' -- does table scan

    select FirstName, EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%' -- table scan

    --removing firstname

    select EmailAddress FROM omar.dbo.a123 where EmailAddress like 'ca%' -- Index seek

    select EmailAddress FROM omar.dbo.a123 where LEFT(EmailAddress,2) = 'ca' --Index Scan

    When you pull FirstName out tof the query, the NC Index is all that is needed to satisfy your query, it is called a covering index.

    When you add FirstName to the query, SQL has to go to the table for this information. It also has determined that scanning the table is going to be faster than using the index, that is why you get a table scan.

  • http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you so much you all.

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

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