Improve/optimize LEFT JOIN in SQL Server

Question

I have a decently long (10 tables, about 60k records each) query, all of those tables are joined using a left join since they could contain null values (all of them).

I'm seeing a huge performance hit and I tracked it down to this bit of code.

SELECT * 
FROM MAIN_TABLE d 
LEFT JOIN INSURANCES i on i.IMREDEM_CODE = d.IMREDEM_CODE 
                      and i.INS_TERMINATIONDATE IS NULL 
                      and INS_RANK = 0
                      and i.IMREINS_CODE = (SELECT TOP 1 IMREINS_CODE 
                                              from INSURANCES i2
                                             WHERE i2.IMREDEM_CODE = i.IMREDEM_CODE 
                                               and i2.INS_TERMINATIONDATE IS NULL 
                                               and i2.INS_RANK = 0
                                          ORDER BY TAG_SYSTEMDATE DESC)

Basically what I am needing to do is that the insurance table could contain 0 or many records because when they update the insurance it performs an insert not an update for audit purposes. So I have to join the table twice, on a left join. Further I need to do this query twice for primary and secondary insurance (primary is rank = 0 and secondary is rank =1. The IMREDEM_CODE is a PK for the D table and a FK for the i table.

The answer is here:

left join INSURANCES i on i.IMREDEM_CODE = d.IMREDEM_CODE 
and i.IMREINS_CODE = (SELECT max(imreins_code) FROM INSURANCES i2  WHERE i2.IMREDEM_CODE = i.IMREDEM_CODE and i2.INS_TERMINATIONDATE IS NULL and i2.INS_RANK = 0)

Answer 1

While I'm sure you may be able to optimize this even further, I have taken a quick stab at it. Your execution plan will do 3 Table Scans. You can try this which will reduce that to 2:

SELECT *
FROM Main_Table d
    LEFT JOIN 
        (SELECT TOP 1 *
         FROM Insurances 
         WHERE INS_TERMINATIONDATE IS NULL 
             AND INS_RANK = 0
         ORDER BY TAG_SYSTEMDATE DESC) i on i.IMREDEM_CODE = d.IMREDEM_CODE;

There is probably a better approach than TOP 1 and Order By, but I'm getting tired and can't think of it at the moment. Either way, this is definitely more efficient.

Here is the SQL Fiddle with both sets of queries. You can see the execution plan in each.

Good luck.