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.
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
.