Why is this SQL statement very slow?

Question

I've got a table with about 1 million records (running SQL Server 2008 Web). I've got a search routine which tries to match on product code as well as product description. However in some circumstances it's very slow. Below is (cut-down) sql statement:

WITH AllProducts AS (
  SELECT       p.*, Row_Number() OVER (ORDER BY ProductId) AS RowNumber
  FROM        Product AS p 
    WHERE p.IsEnabled=1 AND
    (
      p.BaseSku = 'KPK-3020QWC-C' -- this on its own is fast
      OR
      CONTAINS(p.FreeTextStrings, '"KPK-3020QWC*"') -- and this on its own is fast, but not both
    )
) SELECT * FROM AllProducts        
  WHERE RowNumber BETWEEN 1 AND 20;

Note that if i just compare on [p.BaseSku = 'KPK-3020QWC-C'] or [CONTAINS(p.FreeTextStrings, '"KPK-3020QWC*"')] individually (but not both) its instant. And if i compare them together it takes ages (several minutes) - and returns just one row.

IsEnabled and BaseSku are indexed, and FreeTextStrings is FTS-indexed.

I remember this was working fineb efore.

Can anybody shed any light on this and suggest some solutions?

Execution plan file is available here: http://wiki.webgear.co.nz/GetFile.aspx?File=Temp%5cSearch%20Test.sqlplan.zip

Answer 1

or is notoriously slow on SQL Server. It's aggravating, to say the least.

Try splitting it up into two queries with a union:

WITH AllProducts AS (
  select *, Row_Number() OVER (ORDER BY ProductId) AS RowNumber
  from (
  SELECT       p.*
  FROM        Product AS p 
    WHERE p.IsEnabled=1 AND
      p.BaseSku = 'KPK-3020QWC-C' 
  UNION
  SELECT       p.*
  FROM        Product AS p 
    WHERE p.IsEnabled=1 AND
      CONTAINS(p.FreeTextStrings, '"KPK-3020QWC*"')
  )
) SELECT * FROM AllProducts        
  WHERE RowNumber BETWEEN 1 AND 20;

Answer 2

This seems to work well:

WITH AllProducts AS (
  SELECT       p.*, Row_Number() OVER (ORDER BY ProductId) AS RowNumber
  FROM        Product AS p 
    WHERE p.IsEnabled=1 AND
    (
      CONTAINS(p.BaseSku, 'KPK-3020QWC-C') /* instead of p.BaseSku = 'KPK-3020QWC-C' */
      OR
      CONTAINS(p.FreeTextStrings, '"KPK-3020QWC*"')
    )
) SELECT * FROM AllProducts        
  WHERE RowNumber BETWEEN 1 AND 20;

(I already had BaseSku FTS-indexed)