Best way to short circuit a full text query

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?

Answer 1

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

Answer 2

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

 

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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