I am Using SQL SERVER 2008 R2
I just came across APPLY in SQL and loved how it solves query problems for so many cases,
Many of the tables I was using 2 left join to get the result, I was able to get in 1 outer apply.
I have small amount of data in my local DB tables and after deployment the code is supposed to run on data atleast 20 times big.
I am concerned that outer apply might take longer than the 2 left join conditions for large amount of data,
Can anyone tell how exactly apply works and how will it effect the performance in very large data, If possible some proportional relations with size of each table like proportional to n1^1 or n1^2 ... where n1 is number of rows in table 1.
Here is the query with 2 left join
select EC.*,DPD.* from Table1 eC left join
(
select member_id,parent_gid,child_gid,LOB,group_gid,MAX(table2_sid) mdsid from Table2
group by member_id,parent_gid,child_gid,LOB,group_gid
) DPD2 on DPD2.parent_gid = Ec.parent_gid
AND DPD2.child_gid = EC.child_gid
AND DPD2.member_id = EC.member_id
AND DPD2.LOB = EC.default_lob
AND DPD2.group_gid = EC.group_gid
left join
Table2 dpd on dpd.parent_gid = dpd2.parent_gid
and dpd.child_gid = dpd2.child_gid
and dpd.member_id = dpd2.member_id
and dpd.group_gid = dpd2.group_gid
and dpd.LOB = dpd2.LOB
and dpd.table2_sid = dpd2.mdsid
Here is the query with outer apply
select * from Table1 ec
OUTER APPLY (
select top 1 grace_begin_date,retroactive_begin_date,Isretroactive
from Table2 DPD
where DPD.parent_gid = Ec.parent_gid
AND DPD.child_gid = EC.child_gid
AND DPD.member_id = EC.member_id
AND DPD.LOB = EC.default_lob
AND DPD.group_gid = EC.group_gid
order by DPD.table2_sid desc
) DPD