CODEWITHSUNDEEP
sqlsql-serverquery-optimizationsql-tuningquery-tuning
Sparrow
Sparrow

Reputation: 355

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.

Upvotes: 1

Views: 133

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 1

Related Questions