Hi,
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.