472,129 Members | 1,702 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,129 software developers and data experts.

When will a LIKE expression use an index?

I have a stored procedure that is taking a longer than is reasonable to execute, and I have isolated the part that is costing me the most. It is the part that uses a LIKE expression. Here is the sub query in question:

@partial_vernacular is the stored procedure parameter that contains the text I want to match on.

DECLARE @searchlike nvarchar(100);
SET @searchlike = @partial_vernacular +'%';

SELECT * FROM dbo.ls_vernacular_word_item_occurrances
WHERE word LIKE @searchlike

I have read that if a LIKE query uses the form WHERE term LIKE 'mytext%', that should prompt use of an index. Will that work in this situation where the text is passed in as a parameter? I can't see that this behaves any differently performance wise than when I set my search text this way:

SET @searchlike = '%' + @partial_vernacular +'%';

The [word] field is defined as varchar(50). Does the field data type have any bearing on this?
Mar 15 '10 #1
2 1123
2,878 Expert 2GB
Is your [WORD] column in your index?

~~ CK
Mar 15 '10 #2
Yes, the table does have an index on the column in question. The table contains about 100,000 records, and since recently adding the LIKE expression to the procedure, I have lost about half a second to a second (this is part of an auto-suggestion feature, which is why the speed is important to me).
Mar 16 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by Dima Tkach | last post: by
45 posts views Thread by Debashish Chakravarty | last post: by
65 posts views Thread by PengYu.UT | last post: by
23 posts views Thread by Bob Greschke | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.