April 8, 2008 at 2:11 pm
I am creating a stored proc to search a table by a name column. Very simple, "select col1, col2, col3, name, address from table A where name like @SearchString + '%' ". If I run the query in a query window using the exact string I am searching for the query will return the results (500 rows) in about a second. When I execute the same query as a stored proc using the parameter, it takes almost a minute and a half. There is a non-clustered index on the column, and there are about thirty million rows (30,000,000) in the table. How can this be sped up? I ran the database engine tuning advisor and it suggested an additional index. I created the index as suggested, but I do not see any added benefit.
April 8, 2008 at 2:20 pm
Assuming @SearchString itself doesn't have a leading wildcard, you should be able to get something of a performance boost out of a covering index.
something like
create nonclustered index ix_MyTable on MyTable(name) include (col1,col2,col3)
Of course - the optimizer may choose not to use the index if it thinks it can do it faster, but this index should help it quite a bit, since it would save it the bookmarks lookups.....
If @searchstring DOES have a leading %, then I'm not sure anything can be done to speed that up with traditional indexes, since that will force some kind of scan of every index.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 2:26 pm
If the stored procedure was not re-compiled after the addition of the index it is not guaranteed to use the index. Did the execution plan change? Try running the sp with the With ReCompile option if the index is not being used and re-check the execution plan.
Matt is right that if you have a leading '%' an index probably won't help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2008 at 2:46 pm
I am adding the index now, and I have changed the stored proc to use the ReCompile option. Once the index has completed it's build I will try the stored proc again and check the execution plan again.
April 9, 2008 at 2:52 pm
Wow! It took eight hours to create that index, but it was well worth it. The results come back in about a second now.
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply