CODEWITHSUNDEEP
sqlsql-server
Muxa
Muxa

Reputation: 5643

Why is this SQL statement very slow?

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

Upvotes: 8

Views: 8503

Answers (3)

goku_da_master
goku_da_master

Reputation: 4317

Make sure all necessary indexes are in place. I had the same problem with an or clause in one of my queries and creating a NONCLUSTERED INDEX with INCLUDE columns fixed the performance.

After further testing, it was the INCLUDE columns part of the index that really fixed the performance problem. Here's what I did to determine the problem and how to fix it:

Use the Execution Plan to help you creating the missing indexes:

Without the index the query was taking 2+ min when it should have ran in a few milliseconds. So I compared execution plans of the query with and without the or clause in SSMS and it wasn't obvious what I needed to do (mostly due to my lack of understanding the execution plans).

But if you look above the execution plan in green text, SSMS may tell you to create a nonclustered index. Hmm... worth a shot. So I created the index and problem solved! You can right click the "CREATE INDEX" query and select "Missing Index Details...". This will open a new tab with the full query for you to run. Just give it a name.

Upvotes: 0

Muxa
Muxa

Reputation: 5643

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)

Upvotes: 0

Eric
Eric

Reputation: 95243

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;

Upvotes: 14

Related Questions