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).

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.