Variable in select statement prevents use of index.

Question

Variable in select statement prevents use of index.

rsmayer

SSC Enthusiast

Points: 116
More actions
March 10, 2005 at 8:27 am

#362225

I'm encountering a problem in a procedure and I've narrowed it down to the following which I can duplicate in query analyzer.
I am querying a table with 50 million rows. The following statement in query analzer completes in a few milliseconds:
SELECT DOCKEYID FROM DOCKEY WHERE DOCNBR like '12355%'
But the following sequence of statements takes 30-40 minutes (if it returns at all).
declare @DocNo varchar(15)
set @DocNo = '12355%'
SELECT DOCKEYID FROM DOCKEY WHERE DOCNBR like @DocNo
Explain plan on the one that works indicates use of a index that has DOCNBR as its first column:
DocKey.DocumentIndex 7%
Bookmark Lookup (??) (93%)
Select (0%)
Explain plan on the slow version indicates full table scan
Table Scan (91%)
Select (9%)
Any ideas on why these statements behave differently and how I can get the one that uses a variable to use the index?
Thanks

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

There is a thread on this forum that has been started a few days back about parameter sniffing and how sql server wasn't always utilizing the most optimized plan because of that. In this case (SELECT DOCKEYID FROM DOCKEY WHERE DOCNBR like '12355%') you tell sql server to look for a specific number which obviously is indexed allowing for great performance. But if you use "set @DocNo = '12355%'". The optimizer must assume the possibility that the parameter can be set to "%12355%", hence requiring a scan.

Have you tried this variation?

create proc MyProc @MyParam_id as int

as

set nocount on

SELECT DOCKEYID FROM DOCKEY WHERE DOCNBR like CAST(@MyParam_id as varchar(15)) + '%'

go

I think that this would give the optimizer a chance to see that the index can be used in almost any cases since the query will never be like '%343??%'. But I have no real good test case to validate this.

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 2

You could use an index hint:

SELECT DOCKEYID FROM DOCKEY (INDEX(YourIdexName)) WHERE DOCNBR like @DocNo

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

I don't know if this would perform better in QA, but it's worth a shot:

Set @sql = 'select DOCKEYID from DOCKEY'

+ ' where DOCNBR = ' + @Docno

exec @rval = @sql

For a little more sophistication so that you can call it from an SP, try:

declare @qch char(1)

set @qch = char(39) --a single quote

Set @sql = 'select DOCKEYID from DOCKEY'

+ ' where DOCNBR = ' + @qch + @Docno + '%' + @qch

exec @rval = @sql

-----
[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]

rsmayer SSC Enthusiast Points: 116 More actions · Answer 4

Thanks for the replies. Remi's suggestion won't work for me since althought I used integer characters as the string in my example, the column is actually alphanumeric.

Peter's suggestion of the index hint seems to be doing the trick.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I never said you couldn't change the parameter type to varchar

.

But I never said it would speed it up either.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Any here's a link on dynamic sql and why it should not be used :

The Curse and Blessings of Dynamic SQL

Make sure you understand the consequences on dynamic sql before implementing this solution.