WHERE in Sql, combining two fast conditions multiplies costs many times

Question

I have a fairly complex sql that returns 2158 rows' id from a table with ~14M rows. I'm using CTEs for simplification.

The WHERE consists of two conditions. If i comment out one of them, the other runs in ~2 second. If i leave them both (separated by OR) the query runs ~100 seconds. The first condition alone needs 1-2 seconds and returns 19 rows, the second condition alone needs 0 seconds and returns 2139 rows.

What can be the reason?

This is the complete SQL:

WITH fpcRepairs AS
(
    SELECT FPC_Row = ROW_NUMBER()OVER(PARTITION BY t.SSN_Number ORDER BY t.Received_Date, t.Claim_Creation_Date, t.Repair_Completion_Date, t.Claim_Submitted_Date)
    ,   idData, Repair_Completion_Date, Received_Date, Work_Order, SSN_number, fiMaxActionCode, idModel,ModelName
    ,   SP=(SELECT TOP 1 Reused_Indicator FROM tabDataDetail td INNER JOIN tabSparePart sp ON td.fiSparePart=sp.idSparePart
            WHERE td.fiData=t.idData
            AND (td.Material_Quantity <> 0) 
            AND (sp.SparePartName = '1254-3751'))
    FROM   tabData AS t INNER JOIN
       modModel AS m ON t.fiModel = m.idModel 
    WHERE (m.ModelName = 'LT26i') 
    AND EXISTS(
        SELECT  NULL 
        FROM    tabDataDetail AS td 
        INNER JOIN tabSparePart AS sp ON td.fiSparePart = sp.idSparePart
        WHERE  (td.fiData = t.idData) 
        AND (td.Material_Quantity <> 0) 
        AND (sp.SparePartName = '1254-3751')
    ) 
), needToChange AS
(
    SELECT idData FROM tabData AS t INNER JOIN
       modModel AS m ON t.fiModel = m.idModel
    WHERE (m.ModelName = 'LT26i') 
    AND EXISTS(
        SELECT  NULL 
        FROM    tabDataDetail AS td 
        INNER JOIN tabSparePart AS sp ON td.fiSparePart = sp.idSparePart
        WHERE  (td.fiData = t.idData) 
        AND (td.Material_Quantity <> 0) 
        AND (sp.SparePartName IN ('1257-2741','1257-2742','1248-2338','1254-7035','1248-2345','1254-7042'))
    ) 
)
SELECT t.idData
FROM tabData AS t INNER JOIN modModel AS m ON t.fiModel = m.idModel
INNER JOIN needToChange ON t.idData = needToChange.idData  -- needs to change FpcAssy
LEFT OUTER JOIN fpcRepairs rep ON t.idData = rep.idData
WHERE   
rep.idData IS NOT NULL          -- FpcAssy replaced, check if reused was claimed correctly
AND rep.FPC_Row > 1             -- other FpcAssy repair before
AND (
    SELECT SP FROM fpcRepairs lastRep
    WHERE lastRep.SSN_Number = rep.SSN_Number
    AND lastRep.FPC_Row = rep.FPC_Row - 1
) = rep.SP                      -- same SP, must be rejected(reused+reused or new+new)
OR      
rep.idData IS NOT NULL          -- FpcAssy replaced, check if reused was claimed correctly
AND rep.FPC_Row = 1             -- no other FpcAssy repair before
AND rep.SP = 0                  -- not reused, must be rejected
order by t.idData

Here's the execution plan:

Download: http://www.filedropper.com/exeplanfpc

Did you try to use UNION of 2 queries separately instead of OR condition? — hgulyan, Aug 23 '12 at 11:59
@hgulyan: If you'd post it as answer i would at least upvote it. It needs only 3 seconds with a `UNION ALL`. Why? — Tim Schmelter, Aug 23 '12 at 12:06
ok, thanks. I just wasn't sure if it was your case. Try UNION too, instead of UNION ALL :) — hgulyan, Aug 23 '12 at 12:10
@hgulyan: Why shall i use `UNION`? `UNION ALL` is faster and both conditions are mutually exclusive(FPC_Row = 1 and FPC_Row > 1). — Tim Schmelter, Aug 23 '12 at 12:13
I don't understand the use of left join. Unmatched rows are thrown away anyway by any `rep.xxx` condition. — Nikola Markovinović, Aug 23 '12 at 12:15
@TimSchmelter - Re: Why? See http://stackoverflow.com/q/5901791/73226 — Martin Smith, Aug 23 '12 at 12:18
@NikolaMarkovinović: Actually there's a third condition which starts with `rep.idData IS NULL`. I just didn't want to overcomplicate it unnecessarily. — Tim Schmelter, Aug 23 '12 at 12:20

score 3 · Accepted Answer · edited May 23 '17 at 11:56

Try to use UNION ALL of 2 queries separately instead of OR condition.

I've tried it many times and it really helped. I've read about this issue in Art Of SQL .

Read it, you can find many useful information about performance issues.

UPDATE:

Check related questions

UNION ALL vs OR condition in sql server query

http://www.sql-server-performance.com/2011/union-or-sql-server-queries/

Can UNION ALL be faster than JOINs or do my JOINs just suck?

Check Wes's answer

The usage of the OR is probably causing the query optimizer to no longer use an index in the second query.

WHERE in Sql, combining two fast conditions multiplies costs many times

1 Answers1