Query optimization in SQL Server

Question

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.

lakhs - please use internationally known units of measure - thousand, million, billion ..... also: can you please show us the graphical execution plan for the query? — marc_s
– marc_s, Commented Aug 5, 2015 at 11:18

Gordon Linoff · Accepted Answer · 2015-08-05 11:20:37Z

1

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.

answered Aug 5, 2015 at 11:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sparrow Over a year ago

1st solution is not having any performance improvement. But the second one is working with few changes. Thank you.

Collectives™ on Stack Overflow

Query optimization in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related