Optimizing multiple Outer Apply

rowansal 2015-03-30 20:51:16

I am trying to get this query to complete and am starting to think I need to break it up. I'm just not sure how to ensure I get the same results as this query but doing it a more efficient way. Any ideas?

Aaron Bertrand 2015-03-30 20:55:37
Can you upload a non-anonymized, post-execution plan? In the current version of Plan Explorer, anonymization strips the statements, so there is no way anyone can give you useful advice on how to break a query up.
SQLkiwi 2015-03-31 08:38:11
If a non-anonymized plan isn't possible, can you provide at a post-execution anonymized plan? It might be possible to say something useful based on that.
rowansal 2015-03-31 15:06:57
The example I am using might be easier to follow written here than the plan:

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)