Query Optimization required - Subquery using Inner Join

Question

Could anybody advise how can I optimize this query below please?

SELECT
IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName
...
-- ### I would like to optimize this by using inner join ##--
, (SELECT MAX(CPH.[iTermNo]) 
 FROM [tbl_ContractPaymentHistory] CPH
 WHERE CPH.[iContractID] = C.[iContractID]
 AND CPH.[iInternalAccountID] = @InternalAccountID
 AND CPH.[dtTransactionDate] = IT.[dtTransactionDate]) AS TermNo
, (SELECT (MAX(CPH.[iTermNo]) * MAX(CPH.[cMonthlyAmount]))
 FROM [tbl_ContractPaymentHistory] CPH
 WHERE CPH.[iContractID] = C.[iContractID]
 AND CPH.[iInternalAccountID] = @InternalAccountID
 AND CPH.[dtTransactionDate] = IT.[dtTransactionDate]) AS TotalAmount
-- ### end ### --
...
FROM 
 [tbl_InternalTransactions] IT
 INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
 INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
 INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
 INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
 AND IA.[iInternalAccountID] = @InternalAccountID
WHERE 
 IT.[dtTransactionDate] >= @FromDate
 AND IT.[dtTransactionDate] <= @ToDate
...

The query plan can be found here: http://pastebin.com/KHPdEzgi

I have tried to replace the subqueries with the following:

INNER JOIN ( 
SELECT 
 MAX([iContractID]) AS [iContractID] 
 , MAX([iTermNo]) AS [iTermNo] 
 , MAX([cMonthlyAmount]) AS [cMonthlyAmount]
FROM [tbl_ContractPaymentHistory] 
WHERE [iInternalAccountID] = @InternalAccountID
 AND [dtTransactionDate] = IT.[dtTransactionDate] -- not able to use this here
 AND [iContractID] = C.[iContractID] -- not able to use this here
 ) CPH ON CPH.[iContractID] = C.[iContractID]

So I can select [iTermNo] and [cMonthlyAmount]

However, I am not able to use columns from another tables in the inner join subquery. I am getting the following errors: The multi-part identifier "IT.dtTransactionDate" could not be bound. The multi-part identifier "C.[iContractID]" could not be bound.

Help would be much appreciated as the query takes an eternity to load at the moment. Thank you.

Answer 1

You could rewrite with a LEFT JOIN:

SELECT
 IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName
...
-- ### unchanged up to here ##--
, G.TermNo
, G.TermNo * G.MaxMonthlyAmount AS TotalAmount
-- ### end ### --
FROM 
 [tbl_InternalTransactions] IT
 INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
 INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
 INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
 INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
 AND IA.[iInternalAccountID] = @InternalAccountID
-- ### the two subqueries converted to a LEFT JOIN ###
 LEFT JOIN
 ( SELECT CPH.iContractID
 , CPH.dtTransactionDate
 , MAX(CPH.iTermNo) AS TermNo
 , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
 FROM tbl_ContractPaymentHistory CPH
 WHERE CPH.iInternalAccountID = @InternalAccountID
 GROUP BY CPH.iContractID
 , CPH.dtTransactionDate
 ) G
 ON G.iContractID = C.iContractID
 AND G.dtTransactionDate = IT.dtTransactionDate
-- ### end of changes ###
WHERE 
 IT.[dtTransactionDate] >= @FromDate
 AND IT.[dtTransactionDate] <= @ToDate
...

or using OUTER APPLY:

-- ### the two subqueries converted to an OUTER APPLY ###
 OUTER APPLY
 ( SELECT MAX(CPH.iTermNo) AS TermNo
 , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
 FROM tbl_ContractPaymentHistory CPH
 WHERE CPH.iContractID = C.iContractID
 AND CPH.iInternalAccountID = @InternalAccountID
 AND CPH.dtTransactionDate = IT.dtTransactionDate
 ) G
-- ### end of changes ###

---

These are just rewritings along the lines you tried. Not at all sure if they improves efficiency. They will probably do but the main bottleneck may be elsewhere (indexes).

• Francisco: Thank you @ypercube. Re-writing the query using LEFT JOIN does improve the efficiency of it. I'll stick with this solution.

Answer 2

Since you have identified that the sub-select (SELECT ...) CPH cannot join with higher level nesting, how about changing your sample rewrite as something like:

INNER JOIN ( 
 SELECT 
 MAX([iContractID]) AS [iContractID] 
 , MAX([iTermNo]) AS [iTermNo] 
 , MAX([cMonthlyAmount]) AS [cMonthlyAmount]
 FROM [tbl_ContractPaymentHistory] 
 WHERE [iInternalAccountID] = @InternalAccountID
 ) AS CPH
WHERE CPH.[iContractID] = C.[iContractID]
 AND CPH.[dtTransactionDate] = IT.[dtTransactionDate]

The optimizer will consider the whole plan when making optimizing decisions, so you should not be immediately alarmed that the compare of dtTransactionDate is outside of the inner SELECT.

Obviously this code is not complete. If you want someone to test the code consider creating a SQL Fiddle script (http://sqlfiddle.com/) and provide some test data.