May 5, 2005 at 9:48 am
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%'
May 6, 2005 at 11:23 am
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
May 6, 2005 at 12:07 pm
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
May 6, 2005 at 12:31 pm
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
May 6, 2005 at 3:32 pm
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
May 6, 2005 at 3:50 pm
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
May 9, 2005 at 7:20 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply