Внутреннее соединение во внешнем соединении, ведущее к снижению производительности, что представляет собой другой подход?

Трудно не иметь ничего для тестирования, поэтому просто рассматривайте это как отправную точку.

SELECT
    sp.SNAPSHOT_PERIOD_START_DATETIME,
    sp.SNAPSHOT_PERIOD_END_DATETIME,
    aes.APPLICATION_RID,
    aes.ESTATE_RID,
    aes.TRUSTEE_NUMBER,
    aes.TRUSTEE_TYPE,
    aes.TEAM_CODE,
    saoh.POSITION,
    saoh.DATE_FROM,
    saoh.DATE_TO,
    saoh.ERROR_CONDITION,
    (select USER_ID
        from [DEV_STAGING].[dbo].[STAGE_STAF_ACTION_OFFICERS] sao
        where sao.DATE_FROM = saoh.DATE_TO
            and (select ESTATE_RID from Stage_App_Estate_Statuses where TEAM_CODE = sao.POSITION) = aes.ESTATE_RID
    )as PRIOR_CASE_OFFICER --Even if this approach doesnt help, keep the original join as outer because there might not be a prior_case_officer
FROM
    Stage_App_Estate_Statuses aes
    /This should be inner joining to the staging table that links Case officers to team codes by region/
    INNER JOIN [DEV_STAGING].[dbo].[STAGE_STAF_ACTION_OFFICERS] saoh 
        ON aes.TEAM_CODE = saoh.POSITION 
        AND LEFT(aes.ESTATE_RID,3) = LEFT(saoh.ACTION_OFFICER_RID,3)
            /Standard snapshot period new join for MonthlyITS and yearlyTIS/
    INNER JOIN (select

                    SNAPSHOT_PERIOD_START_DATETIME,
                    SNAPSHOT_PERIOD_END_DATETIME,

                from Stage_Snapshot_Period_New
                where 
                    SNAPSHOT_PERIOD_IS_FINALISED_INDICATOR = 'No'
                    and SNAPSHOT_PERIOD_TYPE_NAME in ('MonthlyITS','YearlyITS')
                ) sp
        ON change_date < sp.SNAPSHOT_PERIOD_END_DATETIME 

В этом случае вы можете превратить «внутреннее соединение во внешнее соединение» в несколько левых соединений, хотя ваше предложение where затем должно будет обрабатывать ситуации, с которыми имело дело внутреннее соединение в подзапросе, например:

... query same up to the left join ...
            /*This should be inner joining to App_Estate_Statues again to get the previous UserID*/
            LEFT OUTER JOIN [dbo].[STAGE_STAF_ACTION_OFFICERS] saoch
               ON saoh.DATE_TO = saoch.DATE_FROM
                 AND saoch.POSITION = aes.TEAM_CODE
            LEFT OUTER JOIN Stage_App_Estate_Statuses a ON
                 a.TEAM_CODE = saoch.POSITION
                 AND a.ESTATE_RID = aes.ESTATE_RID
    WHERE (saoch.ACTION_OFFICER_RID IS NULL)
       OR (saoch.ACTION_OFFICER_RID IS NOT NULL AND a.ESTATE_STATUS_RID IS NOT NULL)

Я упростил таблицы, удалив ненужные биты, и добавил несколько фиктивных данных; с фиктивными данными, которые я использовал, этот запрос возвращает те же результаты, что и исходный, но другой план запроса - это означает, что вам придется протестировать его, чтобы определить, работает ли он лучше или нет.

Вот SQLFiddle, который я использовал.

Мой окончательный рабочий код после вдохновения из ответа выше.

WITH curr AS
    (SELECT est.ESTATE_RID
        ,est.TEAM_CODE
        ,staff.POSITION
        ,ISNULL(staff.USER_ID, su.System_User_ID) as USERID
        ,est.CHANGE_DATE
        ,ROW_NUMBER() OVER (ORDER BY est.ESTATE_RID DESC, est.CHANGE_DATE ASC) AS RowNum
        FROM Stage_App_Estate_Statuses est
            LEFT OUTER JOIN Stage_STAF_ACTION_OFFICERS staff ON
            est.TEAM_CODE = staff.POSITION and
            est.CHANGE_DATE BETWEEN staff.DATE_FROM AND staff.DATE_TO
            LEFT OUTER JOIN Stage_System_User_Mapping su on
            est.TEAM_CODE = su.System_User_ID
        WHERE CHANGE_TYPE LIKE '%T%'
    )
SELECT  curr.ESTATE_RID, 
        curr.TEAM_CODE,
        ISNULL(curr.USERID,curr.TEAM_CODE) AS Current_UserID,
        prev.USERID AS Previous_UserID,
        curr.CHANGE_DATE as Date_From,
        helper.CHANGE_DATE as Date_To
FROM curr
LEFT OUTER JOIN curr prev ON    curr.RowNum = (prev.RowNum + 1) and
                                curr.ESTATE_RID = prev.ESTATE_RID                           
LEFT OUTER JOIN curr helper ON curr.RowNum = (helper.RowNum - 1) and
                                curr.ESTATE_RID = helper.ESTATE_RID

ORDER BY ESTATE_RID ASC, curr.CHANGE_DATE ASC