CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Wesley Skeen
Wesley Skeen

Reputation: 1214

OR in WHERE statement slowing things down dramatically

I have the following query that finds customers related to an order. I have a legacy ID on the customer so I have to check old id (legacy) and customer id hence the or statement

SELECT  
   c.Title, 
   c.Name   
  FROM productOrder po   
      INNER JOIN Employee e ON po.BookedBy = e.ID
      CROSS APPLY (
      SELECT TOP 1 *
      FROM Customer c 
      WHERE(po.CustID = c.OldID OR po.CustID = c.CustID)    
      ) c

  GROUP BY     
  c.CustomerId, c.Title, c.FirstName, c.LastName

if I remove the OR statement it runs fine for both situations. There is an index on customer id and legacy.

Upvotes: 1

Views: 64

Answers (1)

mvp
mvp

Reputation: 116317

For table customer, you need to create separate indexes on columns oldid and custid. If you already have clustered index on custid, then add index on oldid as well:

CREATE INDEX customer_oldid_idx ON customer(oldid);

Without this index, search for oldid in this clause:

 WHERE (po.CustID = c.OldID OR po.CustID = c.CustID)

will have to use full table scan, and that will be super slow.

Upvotes: 1

Related Questions