3

This question already has an answer here:

I came across a performance issue and can’t quite understand what causes it. It has to do with the order in which the joins are done in my query. The predicates on the join is always the same, but when I move a join from the bottom to the top of the list of joins it performs much faster (approx 3 seconds). In contrast when I have it in the position right before the cross apply (see query) it is long running, I’ve left it for 30+ seconds before I stopped the execution. [I am running this on SQL server 2016 with legacy cardinality estimator]

When I compare the execution plans, I can see that it's using different indexes and doing a Hash Match in the slower version and a Nested Loop in the version that runs quickly. After doing some searching I read that the order in which Left Joins are done does matter (although I couldn't find a good explanations as to why given my scenario).

Just to experiment I changed my Left Join to an Inner Join. I still had performance differences depending on where the Join is placed. Can anyone provide me any insight as to how/why SQL server will change the execution plan to have such a performance hit based solely on the location of a Join? I was under the impression that the order of the joins did not matter. Please note the object in the join i’m moving around is a view which contains inner joins, but even changing this to a table produces similar performance differences depending on its location. Also, the part of the query being affected are NOT tables in my view or in the predicated of the join that i’m moving around.

The join being moved is :LEFT JOIN email.vwConvertedEmailsDelimited ON vwConvertedEmailsDelimited.ClientID = Clients.ClientID

Query A - Fast:

Actual plan

SELECT Clients.ClientID,
FirstName,
LastName,
Email
FROM dbo.Clients
LEFT JOIN email.vwConvertedEmailsDelimited ON vwConvertedEmailsDelimited.ClientID = Clients.ClientID
INNER JOIN dbo.MembershipContracts ON MembershipContracts.ClientID = Clients.ClientID
INNER JOIN dbo.BilledItemsMemberships ON BilledItemsMemberships.MembershipContractID = MembershipContracts.MembershipContractID
INNER JOIN dbo.BilledItems ON BilledItems.BilledItemID = BilledItemsMemberships.BilledItemID
INNER JOIN dbo.Disbursements ON Disbursements.BilledItemUID = BilledItems.BilledItemUID
INNER JOIN dbo.Receipts ON Receipts.ReceiptID = Disbursements.ReceiptID
INNER JOIN dbo.MembershipTypes ON MembershipTypes.MembershipTypeID = MembershipContracts.MembershipTypeID
CROSS APPLY ( SELECT TOP (1) EffectiveDate AS LatestDate ,
                MembershipContractID,
                StatusString,
                EndDate,
                Remarks,
                EffectiveDate,
                ActivePriority
                FROM dbo.MembershipContractStatuses 
                WHERE EffectiveDate <= '2019-02-28' 
                AND MembershipContractStatuses.MembershipContractID =  MembershipContracts.MembershipContractID         
                ORDER BY EffectiveDate DESC,
                StatusPriority DESC ) AS CurrentStatus 
    WHERE Clients.LocationID = 9
    AND StatusString = 'Active'

Query B - Slow:

Estimated plan

SELECT Clients.ClientID,
FirstName,
LastName,
Email
FROM dbo.Clients
INNER JOIN dbo.MembershipContracts ON MembershipContracts.ClientID = Clients.ClientID
INNER JOIN dbo.BilledItemsMemberships ON BilledItemsMemberships.MembershipContractID = MembershipContracts.MembershipContractID
INNER JOIN dbo.BilledItems ON BilledItems.BilledItemID = BilledItemsMemberships.BilledItemID
INNER JOIN dbo.Disbursements ON Disbursements.BilledItemUID = BilledItems.BilledItemUID
INNER JOIN dbo.Receipts ON Receipts.ReceiptID = Disbursements.ReceiptID
INNER JOIN dbo.MembershipTypes ON MembershipTypes.MembershipTypeID = MembershipContracts.MembershipTypeID
LEFT JOIN email.vwConvertedEmailsDelimited ON vwConvertedEmailsDelimited.ClientID = Clients.ClientID
CROSS APPLY ( SELECT TOP (1) EffectiveDate AS LatestDate ,
                MembershipContractID,
                StatusString,
                EndDate,
                Remarks,
                EffectiveDate,
                ActivePriority -- Need this for getting the current contract
                FROM dbo.MembershipContractStatuses 
                WHERE EffectiveDate <= '2019-02-28' 
                AND MembershipContractStatuses.MembershipContractID =  MembershipContracts.MembershipContractID         
                ORDER BY EffectiveDate DESC,
                StatusPriority DESC ) AS CurrentStatus 
    WHERE Clients.LocationID = 9
    AND StatusString = 'Active'

Also putting the join as the last one is fast as well.

Here is the snippet of the execution plan difference.

Execution: Approx 3 seconds

NestedLoop

Execution : > 30 seconds HashMatch

marked as duplicate by Joe Obbish sql-server Mar 1 at 2:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.