Sql-server – Query Optimization required – Subquery using Inner Join

performancequery-performancesql serversql-server-2008-r2

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.

Best Answer

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