CODEWITHSUNDEEP
sqlsql-server
Stephen
Stephen

Reputation: 65

SQL Performance using a COALESCE Function

I have a SQL Query with a join between several tables.

When I run this query it takes 1min 12 however if I add a Coalesce around the accountID I get a massive performace benefit and it runs in 12 minutes. See the full unedited query below.

    SELECT dbo.CaptionMapItem.CaptionMapItemID, 
       dbo.CaptionMapItem.NodeText, 
       CaptionMapItem_1.NodeText AS 'Level1_Caption', 
       CaptionMapItem_2.NodeText AS 'Level2_Caption', 
       dbo.Account.Account, 
       SUM(CASE Tx.CurrencyID WHEN 4 THEN dbo.Tx.AmountGross END) AS 'USD_Total',
       SUM(CASE Tx.CurrencyID WHEN 5 THEN dbo.Tx.AmountGross END) AS 'GBP_Total',
       SUM(CASE Tx.CurrencyID WHEN 6 THEN dbo.Tx.AmountGross END) AS 'CAD_Total',
       SUM(CASE Tx.CurrencyID WHEN 7 THEN dbo.Tx.AmountGross END) AS 'EUR_Total' 
  FROM dbo.CaptionMapItem 
       INNER JOIN dbo.CaptionMapItem AS CaptionMapItem_1 
          ON dbo.CaptionMapItem.CaptionMapItemID = CaptionMapItem_1.ParentID 
       INNER JOIN dbo.CaptionMapItem AS CaptionMapItem_2 
          ON CaptionMapItem_1.CaptionMapItemID = CaptionMapItem_2.ParentID 
       LEFT OUTER JOIN dbo.CaptionMapItemAccount 
         ON CaptionMapItem_2.CaptionMapItemID = dbo.CaptionMapItemAccount.CaptionMapItemID 
       LEFT OUTER JOIN dbo.Account 
         ON dbo.CaptionMapItemAccount.AccountID = dbo.Account.AccountID 
       LEFT OUTER JOIN dbo.Tx 
         ON dbo.Account.AccountID = dbo.Tx.CreditAccountID 
 GROUP BY dbo.CaptionMapItem.CaptionMapItemID, 
          dbo.CaptionMapItem.NodeText, 
          CaptionMapItem_1.NodeText, 
          CaptionMapItem_2.NodeText, 
          dbo.Account.Account 
HAVING dbo.CaptionMapItem.NodeText LIKE '%CashFLow%'

Upon running through query analyser it added a coalesce around the accountID join. However it also reduces much quicker when the coalesce is on any join.

Upvotes: 2

Views: 4125

Answers (1)

Quassnoi
Quassnoi

Reputation: 425491

By wrapping dbo.Account.AccountID into COALESCE, you make the condition unsargable.

SQL Server cannot use nested loops with an index seek on dbo.Account.AccountID anymore and it switches to the index scan with MERGE JOIN or HASH JOIN instead.

Update:

Assuming your CaptionMapItem and CaptionMapItemAccount are key-preserved, try this query:

SELECT  *
FROM    (
        SELECT  dbo.CaptionMapItem.CaptionMapItemID, 
                dbo.CaptionMapItem.NodeText, 
                CaptionMapItem_1.NodeText AS 'Level1_Caption', 
                CaptionMapItem_2.NodeText AS 'Level2_Caption',
                AccountID
        FROM    dbo.CaptionMapItem 
        JOIN    dbo.CaptionMapItem AS CaptionMapItem_1 
        ON      dbo.CaptionMapItem.CaptionMapItemID = CaptionMapItem_1.ParentID 
        JOIN    dbo.CaptionMapItem AS CaptionMapItem_2 
        ON      CaptionMapItem_1.CaptionMapItemID = CaptionMapItem_2.ParentID 
        LEFT JOIN
                dbo.CaptionMapItemAccount
        ON      CaptionMapItem_2.CaptionMapItemID = dbo.CaptionMapItemAccount.CaptionMapItemID 
        LEFT JOIN
                dbo.Account 
        ON      dbo.CaptionMapItemAccount.AccountID = dbo.Account.AccountID 
        WHERE   dbo.CaptionMapItem.NodeText LIKE '%CashFLow%'
        ) qA
OUTER APPLY
        (
        SELECT  SUM(CASE Tx.CurrencyID WHEN 4 THEN dbo.Tx.AmountGross END) AS 'USD_Total',
                SUM(CASE Tx.CurrencyID WHEN 5 THEN dbo.Tx.AmountGross END) AS 'GBP_Total',
                SUM(CASE Tx.CurrencyID WHEN 6 THEN dbo.Tx.AmountGross END) AS 'CAD_Total',
                SUM(CASE Tx.CurrencyID WHEN 7 THEN dbo.Tx.AmountGross END) AS 'EUR_Total'
        FROM    dbo.Tx 
        WHERE   dbo.Tx.CreditAccountID = qA.AccountID
        ) qTX

Upvotes: 2

Related Questions