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?