March 28, 2012 at 2:27 pm
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%'
March 28, 2012 at 2:37 pm
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
March 28, 2012 at 2:40 pm
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
March 28, 2012 at 2:58 pm
I used, following index.
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON omar.dbo.a123 (EmailAddress)
March 28, 2012 at 3:02 pm
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
March 28, 2012 at 3:10 pm
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.
March 28, 2012 at 3:29 pm
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%'
March 28, 2012 at 3:34 pm
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.
March 28, 2012 at 3:40 pm
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.
March 28, 2012 at 3:44 pm
Thanks Lynn Pettis,
I do like your answer, if query optimizer table scan is best rather then index seek.
March 28, 2012 at 3:45 pm
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
Change is inevitable... Change for the better is not.
March 28, 2012 at 3:59 pm
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
March 28, 2012 at 4:13 pm
Munabhai (3/28/2012)
No I donot have any cluster or pirmary or unique key in table, but as Antares686 saidselect 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.
March 28, 2012 at 5:23 pm
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
March 29, 2012 at 8:06 am
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