Query using Index Scan instead of Index Seek

Question

Query using Index Scan instead of Index Seek

kramaswamy

SSCoach

Points: 18181
More actions
April 6, 2009 at 2:24 pm

#203219

Hi all - I've got a pretty simple problem that I can't seem to find any elegant workaround for.
Here's my query:
DECLARE @ContactID INT
DECLARE @LastName2 VARCHAR(100)
DECLARE @FirstName2 VARCHAR(100)
DECLARE @SourceID TINYINT
DECLARE @LastName VARCHAR(100)
DECLARE @FirstName VARCHAR(100)
SELECT TOP 1
@ContactID = cnt_ContactID,
@LastName2 = cnt_LastName,
@FirstName2 = cnt_FirstName
FROM dbo.Contact
WHERE cnt_SourceID = @SourceID
ANDISNULL(cnt_LastName, '') = ISNULL(@LastName, '')
AND ISNULL(cnt_FirstName, '') = ISNULL(@FirstName, '')
This version of the query results in the execution plan:
Now, on the other hand, if I change @SourceID from a variable to a constant,
SELECT TOP 1
@ContactID = cnt_ContactID,
@LastName2 = cnt_LastName,
@FirstName2 = cnt_FirstName
FROM dbo.Contact
WHERE cnt_SourceID = 1
ANDISNULL(cnt_LastName, '') = ISNULL(@LastName, '')
AND ISNULL(cnt_FirstName, '') = ISNULL(@FirstName, '')
I get this execution plan instead:
I cannot for the life of me figure out why the query compiler would choose to do an index scan when given a variable, and an index seek when given a constant.
I know that SQL Server 2008 supports a FORCESEEK hint, however, this server is using SQL Server 2005; are there any options for 2005 to make the compiler use a seek? 'Cause unless I'm just misunderstanding the way this is working, there's no reason why the compiler should choose a scan over a seek.

Viewing 6 posts - 1 through 5 (of 5 total)

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

Hm - interestingly, when I run the analysis against those queries independently of their actual usage, I get the results I posted about earlier.

However, when I run the analysis against the stored proc which uses the query as part of the proc, even with the variable, I still get the index seek - however, this only happened *after* I ran the stored proc; before running it, I got the index scan version, after running it, I now have the index seek version.

Is this normal? And, if so, is it cause for concern at all, or is it just that the estimated execution plan can't really be relied upon for 100% accurate assessments?

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

Your index seek (when you get it) is only on sourceID. FirstName and LastName cannot be part of the index seek operation because they're contained within functions.

Try this and see if it's any better.

AND (cnt_LastName = @LastName OR (cnt_LastName IS NULL AND @LastName IS NULL))

AND (cnt_FirstName= @FirstName OR (cnt_FirstName IS NULL AND @FirstName IS NULL))

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

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Interesting ... that does turn it into an index seek; wasn't aware that functions cause indexes to be unusable. Even though isnull is doing exactly the same thing as using an OR, it still causes it to be slower?

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

kramaswamy (4/6/2009)
wasn't aware that functions cause indexes to be unusable.

It's not that functions make indexes unusable, it's that any function on a column (any at all) means that the predicate is no longer SARGable, ie no longer usable for a seek operation. Doesn't matter if the function doesn't change the values (UPPER in a case-insensitive database).

Edit: You can see that in the exec plan. The predicate on SourceID is listed as a seek predicate, while the other two are listed just as predicates.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

i m sorry Gila but i m redirecting u to my new post ..actually i need ur uprgent help

i hope u will understand

below is the link

http://www.sqlservercentral.com/Forums/Topic695572-360-1.aspx

thanks

bhuvnesh

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)