title: SQL Performance using a COALESCE Function
body: <p>I have a SQL Query with a join between several tables.</p>
  
  <p>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.</p>
  
  <pre><code>    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%'
  </code></pre>
  
  <p>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.</p>