The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX tfemptemp_idx_employeecode_tfsourceid ON dbo.TFEmpTemp (EmployeeCode,TFSourceID);
CREATE INDEX tftimesheettemp_idx_startt_endtim_activi_paymen_branch ON dbo.TFTimesheetTemp (StartTime,EndTime,ActivityCode,PaymentCode,BranchCode);

The optimized query:

SELECT
        1 
    FROM
        dwload.dbo.TF_Full_Backup_AT 
    WHERE
        EXISTS (
            SELECT
                * 
            FROM
                dwload.dbo.TFTimesheetTemp t 
            INNER JOIN
                dwload.dbo.TFEmpTemp e 
                    ON t.EmployeeCode = e.EmployeeCode 
            WHERE
                dwload.dbo.TF_Full_Backup_AT.TFSourceID = e.TFSourceID 
                AND dwload.dbo.TF_Full_Backup_AT.StartTime = t.StartTime 
                AND dwload.dbo.TF_Full_Backup_AT.EndTime = t.EndTime 
                AND dwload.dbo.TF_Full_Backup_AT.ActivityCode = t.ActivityCode 
                AND dwload.dbo.TF_Full_Backup_AT.PaymentCode = t.PaymentCode 
                AND dwload.dbo.TF_Full_Backup_AT.BranchCode = t.BranchCode
        )