Query optimization in SQL Server -

select      t2.entity1id, t1.entity1id        t1   full outer join      t2 on t1.c2 = t2.c2 , t1.c1 = t2.c1 , t1.c3 = 1      ((t1.c1 = 123 ) or (t2.c1 = 123))       , (t1.c3 = 1 or t1.c3 null) 

above query taking 12 seconds in sql server 2014, idea tune query? there indexes on c1,c2,c3 columns.

observation: in above query, when remove condition or (i.e.

select       t2.entity1id, t1.entity1id        t1   full outer join      t2 on t1.c2 = t2.c2 , t1.c1 = t2.c1  , t1.c3 = 1      (t1.c1 = 123) , (t1.c3 = 1 or t1.c3 null) 

then it's returning results in 0 seconds.

each table has around 500'000 records.

first, final condition (t1.c3 = 1 or t1.c3 null) redundant. given join condition, these possible values. so, query is:

select t2.entity1id, t1.entity1id   t1 full outer join      t2      on t1.c2 = t2.c2 , t1.c1 = t2.c1  , t1.c3 = 1 (t1.c1 = 123 ) or (t2.c1 = 123) 

if doesn't have performance, consider breaking 2 queries:

select t2.entity1id, t1.entity1id   t1 left join      t2      on t1.c2 = t2.c2 , t1.c1 = t2.c1  , t1.c3 = 1 t1.c1 = 123 union  select t2.entity1id, t1.entity1id   t2 left join      t1      on t1.c2 = t2.c2 , t1.c1 = t2.c1  , t1.c3 = 1 t2.c1 = 123 

sometimes, optimization of separate subqueries better optimization full outer join.