Optimizing multiple Outer Apply
Select a.*, b.*, c.*, d.*, e.*, f.*, coalesce(g.field1, h.field1, i.field1, j.field1) AS Field1, coalesce(g.field2, h.field2, i.field2, j.field2) AS Field2, coalesce(g.field3, h.field3, i.field3, j.field3) AS Field3, coalesce(g.field4, h.field4, i.field4, j.field4) AS Field4, coalesce(g.field5, h.field5, i.field5, j.field5) AS Field5, ... Field 14 From a left join b on a.a_key1 = b.b_key1 left join c on c.c_key1 = b.b_key2 and c.c_key2 = a.a_key2 left join d on d.d_key1 = b.b_key3 left join e on e.e_key1 = c.c_key3 left join f on f.f_key1 = c.c_key4 OUTER APPLY(SELECT * FROM Lookuptable g WHERE c.lookup1 = g.field1) g OUTER APPLY(SELECT * FROM Lookuptable h WHERE c.lookup2 = h.field2) h OUTER APPLY(SELECT * FROM Lookuptable i WHERE c.lookup3 = i.field3) i OUTER APPLY(SELECT * FROM Lookuptable j WHERE c.lookup4 = j.field4) j
Fields 1 – 4 are lookup keys and 5-14 are appended values needed to exclude on in the next query but we want to hold the values that are found.
I ended up just using top 0 to create the table with the 14 null coalesced fields and then I am looking up from the outer applys and doing a series of update statements.
I am struggling to ensure that the result is what I am after. I am trying to reverse lookup data and append it here for easier exclusion in the follow-up query where I then say:
select * from [last_query_results_stored_to_table] where field5 is null and .. field14 is null
I had to exclude fields 1 – 4 because those fields where just the lookup value to find whether we need to exclude the reverse lookup results. there may not be a match in the lookup table as I have pre-populated that data with the key values (fields 1 – 3) + the 3 combinations of possible results (in the similiar fashion to this, just w 3 iterations rather than 4 and less joins)