locked
Best way to short circuit a full text query RRS feed

  • Question

  • I am looking to incorporate a full text search based on the value of a parameter. If a value is passed in to the parameter, I want to perform the full text search. Otherwise there should be no filtering.

    I was hoping a check for the parameter value of % or null would work to short circuit the where clause. However it doesnt get me the performance boost I was hoping for. A side by side comparison of the execution plans yields a 1% v/s 99% relative cost for the following batch.


    declare @SavedSearch_CustomFieldSearch varchar(100) =
    N'("$$cov_fi_fil_analyst000DBRA$$")'


    dbcc dropcleanbuffers
    -- This has a relative cost of 1%
    select UniqueID from SM where
    Contains (SearchKeywords, @SavedSearch_CustomFieldSearch)


    dbcc dropcleanbuffers
    -- This has a relative cost of 99%
    select UniqueID from SM where
    @SavedSearch_CustomFieldSearch = '%' OR
    Contains (SearchKeywords, @SavedSearch_CustomFieldSearch)
     

    The first query returns in about 10 sec while the second one takes about 40 sec.  The return set is about 4500 uniqueidentifiers from a 115000 record table. SearchKeywords is my text column.

    What is the best way to implement this type of short circuit without using dynamic SQL?



    Wednesday, August 3, 2011 9:30 PM

Answers

  • Ok, I guess the only option left is to construct this query dynamically.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 8:06 AM
    • Marked as answer by Alex Feng (SQL) Friday, August 12, 2011 11:44 AM
    Friday, August 5, 2011 2:58 PM
  • I think the problem is that when using the "OR" or the "CASE" statement, the engine has to run the contains function for each row.

    I am instead going to try to use CONTAINSTABLE and then try to short circuit with the "OR" since the conditions are so straight forward for the optimizer to evaluate. The results look very promising so far..

    set statistics time on
    select SM.UniqueID from SM
    LEFT JOIN CONTAINSTABLE(SM, SearchKeywords, @SavedSearch_CustomFieldSearch) D
    ON D.[Key] = SM.UniqueID
    WHERE
     @SavedSearch_CustomFieldSearch = '%' OR D.[Key] IS NOT NULL
    set statistics time off

    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 8:12 AM
    • Marked as answer by Alex Feng (SQL) Friday, August 12, 2011 11:44 AM
    Friday, August 5, 2011 3:52 PM

All replies

  • One possibility for a shortcut to use case statement, e.g.

    where 1 = case when @SearchStr = '%' then 1 else contains(SearchKeywords, @SearchStr) end
    

    If you only want to consider one field, then may be also

     

    IF @SearchStr = '%'
    
        one query
    
    ELSE
    
       different query
    

    See also this helpful thread with many good links.

     

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, August 4, 2011 3:13 AM
  • Thanks for the links.

    I have about 15-20 parameters going into my queries and hence I cant use the IF

    I rewrote my query using the case statements, and do see some improvements. However nothing beats a direct run of the direct full text query.

    So from my test cases where the full text returns @ 4500 UniqueIDs:
    A direct run of the full text search executes in 713 msec
    A dumb short circuit using the OR executes in 5530 msec
    The case statement short circuit executes in 4363 msec

    Can you please see if I am doing something horribly wrong?
    (I am trying this out on SQL2008 if that matters)

     

    set statistics time off


    declare @SavedSearch_CustomFieldSearch varchar(100) = N'("$$cov_fi_fil_analyst000DBRA$$")'

    dbcc dropcleanbuffers


    set statistics time on
    select UniqueID from SM where Contains(SearchKeywords, @SavedSearch_CustomFieldSearch)
    set statistics time off


    dbcc dropcleanbuffers

    set statistics time on
    select UniqueID from SM where
    (@SavedSearch_CustomFieldSearch = '%' OR CONTAINS(SM.SearchKeywords, @SavedSearch_CustomFieldSearch))
    set statistics time off

    dbcc dropcleanbuffers

    set statistics time on
    select UniqueID from SM where
    1 =
     case
     when @SavedSearch_CustomFieldSearch = '%' then 1
     when CONTAINS(SM.SearchKeywords, @SavedSearch_CustomFieldSearch) then 1
     else 0
     end
    set statistics time off

     

    Friday, August 5, 2011 1:49 PM
  • I suggest to also add option (recompile) to your queries and re-test.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, August 5, 2011 2:31 PM
  • The recompile does not seem to make a difference.

    From the execution plans,

    In the faster one, there is only 1 execution and rebind of the FullTextMatch object.

    I do notice that the number of executions and rebinds is 115000 (which is equal to the number of records in the table) for the slower query.

    It somehow seem to be processing it once for each row.

    Friday, August 5, 2011 2:56 PM
  • Ok, I guess the only option left is to construct this query dynamically.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 8:06 AM
    • Marked as answer by Alex Feng (SQL) Friday, August 12, 2011 11:44 AM
    Friday, August 5, 2011 2:58 PM
  • I think the problem is that when using the "OR" or the "CASE" statement, the engine has to run the contains function for each row.

    I am instead going to try to use CONTAINSTABLE and then try to short circuit with the "OR" since the conditions are so straight forward for the optimizer to evaluate. The results look very promising so far..

    set statistics time on
    select SM.UniqueID from SM
    LEFT JOIN CONTAINSTABLE(SM, SearchKeywords, @SavedSearch_CustomFieldSearch) D
    ON D.[Key] = SM.UniqueID
    WHERE
     @SavedSearch_CustomFieldSearch = '%' OR D.[Key] IS NOT NULL
    set statistics time off

    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 8:12 AM
    • Marked as answer by Alex Feng (SQL) Friday, August 12, 2011 11:44 AM
    Friday, August 5, 2011 3:52 PM