SELECT Query speed varies heavily with different inputs?

Question

I have this stored procedure in my database:

ALTER PROCEDURE [dbo].[sp_FTSearchLocation] 
     @SearchFor nvarchar(200) = null
     ,@StartRow int
     ,@EndRow int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM (
    SELECT TOP (@EndRow)
        *, ROW_NUMBER() OVER (ORDER BY Recommendations DESC, CompanyName) AS num            
    FROM
        dbo.cachedSearchTable    
    WHERE       
        (
               (@SearchFor IS NULL)
            OR (CompanyName like '%' + @SearchFor + '%') 
            OR (Handle like '%' + @SearchFor + '%')
            OR (Activity like '%' + @SearchFor + '%')
        )
    ) As a
    WHERE num > @StartRow

    OPTION (RECOMPILE)
END

dbo.cachedSearchTable has a clustered index with the columns Recommendations DESC, CompanyName. There are no other indexes. I think it is safe to use * because the table cachedSearchTable is constructed to only have columns that are relevant to this query.

For some search strings, this procedure runs very fast. For instance, searching for accountants returns in less than a second. However, others run very slowly: When @SearchFor is set to soup it takes about 6 seconds to return.

The execution plans for each of these look the same:

For Accountants:

ClusteredIndexScan (85%) ->
Segment (15%) ->
ComputeScalar (0%) ->
SequenceProject (0%) ->
Top (0%) ->
Filter (0%) ->
Select (0%).

For Soup:

ClusteredIndexScan (95%) ->
Parallelism (Gather Streams) (5%) ->
Segment (0%) ->
ComputeScalar (0%) ->
SequenceProject (0%) ->
Top (0%) ->
Filter (0%) ->
Select (0%).

However, for accountants, the ClusteredIndexScan has an estimated operator cost of 0.57, while for soup it has a cost of 25.34.

I tried putting 3 non-clustered indexes on the table - one for each of the searched columns - but this did not help.

There are lots of accountants in my database (~4000) and very few with 'soup' in their name (~50). In general, it seems that the query runs fastest when there are lots of possible results to chose from, and slowest when there are very few results to return.

How can I speed up this query? Slowing down the write speed to the table does not matter, but applying more indexes does not seem to help. Can you suggest anything?

Have you try to update statistic? I use to have the similar problem like your problem after i put a huge of number of record to table.I think that fragmentation of statistic not would not be update properly and may be the cause of SQL to get wrong execution plan. just a guess — Pongsathon.keng, Feb 15 '12 at 16:44
It's called parameter sniffing - essentially SQL Server compiles a plan based on the first parameter(s) you passed, while it may have made a different decision with different parameters. Have you compared the execution plans for good and bad executions? See http://stackoverflow.com/questions/1007397/sql-poor-stored-procedure-execution-plan-performance-parameter-sniffing and also get our free Plan Explorer tool which highlights these issues a little better http://sqlsentry.net/plan-explorer/sql-server-query-view.asp — Aaron Bertrand, Feb 15 '12 at 16:44
@AaronBertrand Thanks, I will get your software, it looks useful. The actual stored procedure has lots of extra parameters (all of which may be null), so it has `option(recompiled)` set, which I think means that is recompiles a plan each time. I've edited my question to indicate this. — Oliver, Feb 15 '12 at 16:54
@Pongsathon.keng thats for the tip, I will investigate this. The whole contents of the table changes quite often. — Oliver, Feb 15 '12 at 16:59

score 3 · Accepted Answer · answered Feb 15 '12 at 16:59

3

Searching on LIKE %something will not benefit from a B-tree index. The fact that only 4000 rows take seconds (instead of milliseconds) to search is an additional hint this is the case. Don't get confused by the ClusteredIndexScan in your query plan - this is simply a clustered equivalent of a full table scan.

So, why the difference? Since there are many more accountants then soups, and you are only searching for TOP N rows, the first query will tend to find the N rows earlier than the second one, i.e. by scanning the lesser portion of the table.

You'll either need to rewrite your query to use LIKE something%, or (if possible) use the full-text indexing.

answered Feb 15 '12 at 16:59

Branko Dimitrijevic

50,809
10
93
167

I have not used full text indexing before, and I can't avoid using something that produces results as `LIKE %query%`. Does full-text indexing work fast for these kinds of queries? – Oliver Feb 15 '12 at 17:01
@Oliver Full-text indexing will work on words, not on arbitrary sub-strings. So no - this will not speed-up the general substring search where substring does not necessarily fall on word boundaries. But **if** you know it always will (fall on the word boundary), you should be able to use it to speed-up your search (that's why I said "if possible"). – Branko Dimitrijevic Feb 15 '12 at 17:08
@Oliver Then again, **if** you can organize your data neatly into "keywords", you can normalize your table by extracting the keywords into a separate table and then you will be able to search on `= something` and use normal B-tree indexes. – Branko Dimitrijevic Feb 15 '12 at 17:11
I can't really use keywords, but I think I can assume that most substrings fall on word boundries, so I will look into the full text indexing, thanks. – Oliver Feb 15 '12 at 17:31

SELECT Query speed varies heavily with different inputs?

1 Answers1