sqlsql-serverquery-optimizationsql-tuningquery-tuning

Query optimization in SQL Server


SELECT 
    T2.Entity1Id, T1.Entity1Id  
FROM 
    T1  
FULL OUTER JOIN 
    T2 ON T1.c2 = T2.c2 AND T1.c1 = T2.c1 AND T1.c3 = 1
WHERE 
    ((T1.c1 = 123 ) OR (T2.c1 = 123))  
    AND (T1.c3 = 1 OR T1.c3 IS NULL)

Above query is taking 12 seconds in SQL Server 2014, any idea to tune the query? There are indexes on C1,C2,C3 columns.

Observation: in the above query, when I remove a condition from OR (i.e.

SELECT  
    T2.Entity1Id, T1.Entity1Id  
FROM 
    T1  
FULL OUTER JOIN 
    T2 ON T1.c2 = T2.c2 AND T1.c1 = T2.c1  AND T1.c3 = 1
WHERE 
    (T1.c1 = 123) AND (T1.c3 = 1 OR T1.c3 IS NULL)

then it's returning results in 0 seconds.

Each table has around 500'000 records.


Solution

  • First, the final condition (T1.c3 = 1 OR T1.c3 IS NULL) is redundant. Given the join condition, these are the only possible values. So, the query is:

    SELECT T2.Entity1Id, T1.Entity1Id  
    FROM T1 FULL OUTER JOIN
         T2
         ON T1.c2 = T2.c2 AND T1.c1 = T2.c1  AND T1.c3 = 1
    WHERE (T1.c1 = 123 ) OR (T2.c1 = 123)
    

    If this doesn't have good performance, consider breaking this into two queries:

    SELECT T2.Entity1Id, T1.Entity1Id  
    FROM T1 LEFT JOIN
         T2
         ON T1.c2 = T2.c2 AND T1.c1 = T2.c1  AND T1.c3 = 1
    WHERE T1.c1 = 123
    UNION 
    SELECT T2.Entity1Id, T1.Entity1Id  
    FROM T2 LEFT JOIN
         T1
         ON T1.c2 = T2.c2 AND T1.c1 = T2.c1  AND T1.c3 = 1
    WHERE T2.c1 = 123
    

    Sometimes, the optimization of the separate subqueries is much better than the optimization for the full outer join.