Like with Variable

Question

Like with Variable

CurtM

Mr or Mrs. 500

Points: 572
More actions
May 5, 2005 at 9:48 am

#110874

I have these 2 queries, which perform the same function. The first uses table scans, and the 2nd index seeks. I want to use the variable so I can make the statement in a stored proc. Any ideas of anything I can do? There is a non-clustered index on Member# on both tables. Member# is defined as char(14) on both tables.
declare @memb varchar(11)
set @memb = '123456789'
set @memb = @memb + '%'
select * from member_header
join member_detail on member_header.member# = member_detail.member#
where member_header.member# like @memb
select * from member_header
join member_detail on member_header.member# = member_detail.member#
where member_header.member# like '123456789%'

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

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

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 1

The first query has to do a table scan because you use a variable in the "like" portion. Table scans are always used when you have a like comparison to '%123%' for instance. The execution plan generator doesn't know that your variable has the form '123%'.

If your member# query is always a starts-with type of query, you could use:

... where substring(member_header.member#, 1, len(@memb)) = @memb

Try that, and see if it uses the index.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

CurtM Mr or Mrs. 500 Points: 572 More actions · Answer 2

Thanks for the reply.. I now see why the index is not used, although I still don't like it!

The suggestion did not use the index either. Our solution for the moment was to use dynamic SQL inside the procedure.. Paying the cost for the compile was much less than the cost of the table scan. First time I have ever seen dynamic SQL outperform a stored proc!

Perhaps this is the correct time to use a hint, since we always know that the value passed in will be selective enough to use the index... Or perhaps the best solution would be to redesign the table to separate the suffix from the number, allowing us to index both fields and use a view to see the combined?

Curt

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 3

That is unusual to have dynamic SQL out-perform compiled, but that makes sense, because the comiler and execution plan optimizer know exactly what the "like" comparison is in that case.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Donald Sawford SSCrazy Points: 2778 More actions · Answer 4

Hello,

I have a table where I rely extensively on almost the same type of query. After reading this post, I just tested it again to be sure, but it does always use a clustered index seek on a table of approx. 775000 records.

I tried all the following:

1.

declare @orderid varchar(10)

set @orderid = '525874'

set @orderid = @orderid + '%'

select * from dbo.ocrsports where batchtrack like @orderid

2.

declare @orderid varchar(10)

set @orderid = '525874%'

select * from dbo.ocrsports where batchtrack like @orderid

3.

declare @orderid varchar(10)

set @orderid = '525874'

select * from dbo.ocrsports where batchtrack like @orderid + '%'

4.

select * from dbo.ocrsports where batchtrack like '525874%'

They all 4 did use the clustered index seek on Batchtrack. The only difference was that the 3 using the variable also had a Constant Scan and Compute Scalar (both showing 0 cost) that the 4th query didn't have.

Is it different because I am not using a join, or because I have a clustered Index?

When I first began doing this, I was actually using Where left(rtrim(batchtrack), 6) = @orderid instead of like, and it worked, but changing to like improved the performance very dramatically.

Any ideas why I am not getting table scans with this?

Thanks

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

A clustered index seek will be used with a Like statement, as long as the wildcard is at the end of the search variable and not at the beginning. Switch it around and you'll see completely different behaviour.

If you think about it, this makes sense. The index is still useful, as the data is ordered by 1st character, then 2nd character, then 3rd, etc.

cl

Signature is NULL

CurtM Mr or Mrs. 500 Points: 572 More actions · Answer 6

This may be consistent, the index that is not being used is not clustered. If I have some time today I will try some changes to see if clustering the index will make the difference.