代码之家 › 专栏 › 技术社区 › Stephen

使用COALESCE函数的SQL性能

sql-server sql

Stephen · 技术社区 · 16 年前

我有一个SQL查询,在几个表之间有一个连接。

    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%'

1 回复 | 直到 16 年前

Quassnoi 16 年前

dbo.Account.AccountID 进入 COALESCE

SQL Server 不能在索引查找上使用嵌套循环 dbo。账户帐户编号 MERGE JOIN 或 HASH JOIN

假设你 CaptionMapItem 和 CaptionMapItemAccount

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