Query with combined WHERE clause is slower than two individual WHERE clauses

Question

I'm having a performance problem with a SQL query that is generated by a .NET application.

Basically what the query is doing is: (query1) left join (query2) right join (queries3 to 30) WHERE (query1.ID IS NULL) OR (query3.ID IS NULL AND query4.ID IS NULL AND… queryN.ID IS NULL)

When the query only does WHERE A (query1.ID) the query is fast.
When the query only does WHERE B (query3 to 30) the query is fast
When A and B are a combined WHERE clause with an OR, the query is very slow.

I'm looking for a way to optimize this query without variables or stored procedures.

The query:

SELECT DISTINCT [Table0].[FIELD]
FROM   /*8*/ ([Table0] AS [Table0]
        INNER JOIN
        [XTABLE] AS [XTABLE0]
        ON [Table0].ID = [XTABLE0].ID1
           AND [XTABLE0].ID3 = 52)
       RIGHT OUTER /*10*/ JOIN
       [Table1] AS [Table1]
       /*21*/ /*11*/ ON [XTABLE0].ID2 = [Table1].ID
          AND [XTABLE0].ID3 = 52
       LEFT OUTER JOIN
       ([XTABLE] AS [XTABLE1]
        INNER JOIN
        [Table2] AS [Table2]
        ON [XTABLE1].ID1 = [Table2].ID
           AND [XTABLE1].ID3 = 19
        /*20a*/ INNER JOIN
        [XTABLE] AS [XTABLE2]
        ON [Table2].ID = [XTABLE2].ID1
           AND [XTABLE2].ID3 = 8
        INNER JOIN
        [Table3] AS [Table3]
        ON [XTABLE2].ID2 = [Table3].ID
           AND [XTABLE2].ID3 = 8/*22*/ )
       ON [Table1].ID = [XTABLE1].ID2
          AND [XTABLE1].ID3 = 19
       /*26 */ LEFT OUTER JOIN
       ([XTABLE] AS [XTABLE3]
        ... and tens of similar INNER JOIN blocks
WHERE  (/*13*/ [XTABLE0].ID IS NULL)
       OR (/*25*/ [XTABLE1].ID IS NULL
           AND /*27b*/ [XTABLE3].ID IS NULL
           AND /*27b*/ [XTABLE5].ID IS NULL
          ... and tens of similar lines
           AND /*27b*/ [XTABLE131].ID IS NULL);

Yes, too generic to give your suggestion, also include your explain plan http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — Juan Carlos Oropeza, Nov 16 '15 at 14:24
Mixing left and right joins is just downright confusing, if you can avoid it. — Gordon Linoff, Nov 16 '15 at 14:42

score 0 · Answer 1 · answered Nov 16 '15 at 14:31

You are OUTER JOIN'ing the queries, so, when you start putting stuff in the WHERE clause from the result of the OUTER JOIN table expressions (derived table in this case) then it will more than likely be treat as an INNER JOIN - you can see that by checking the query plan.

Query with combined WHERE clause is slower than two individual WHERE clauses

1 Answers1