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