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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sparrow
  • 355
  • 4
  • 19
  • 1
    *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 Aug 05 '15 at 11:18

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1st solution is not having any performance improvement. But the second one is working with few changes. Thank you. – Sparrow Aug 05 '15 at 11:33