Which one is faster?

Question

Which one is faster?

Munabhai

Default port

Points: 1445
More actions
March 28, 2012 at 2:27 pm

#255693

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%'

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

Munabhai Default port Points: 1445 More actions · Answer 3

I used, following index.

CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber

ON omar.dbo.a123 (EmailAddress)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

Antares686 SSC Guru Points: 125444 More actions · Answer 5

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.

Munabhai Default port Points: 1445 More actions · Answer 6

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%'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 8

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.

Munabhai Default port Points: 1445 More actions · Answer 9

Thanks Lynn Pettis,

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

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)

Munabhai Default port Points: 1445 More actions · Answer 11

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

Munabhai Default port Points: 1445 More actions · Answer 14

Munabhai

Default port

Points: 1445

March 29, 2012 at 8:06 am

#1466362

Thank you so much you all.