SQL Server 2008 - 连接导致性能变慢

Question

我之前提出了以下查询问题，但由于我没有提供查询计划，因此它被投票下降。这一次，我已经提供了查询计划，并希望对如何改进以下查询的建议/评论：

以下查询需要5分钟才能返回约68000条记录。但是，如果我从查询中删除LKP_PrivateSource（即最后一个左连接），则只需1秒即可返回68000条记录。顺便说一句，LKP_PrivateSource表中没有记录。有什么想法是什么原因导致了这个问题吗？

Select 
    Case IsNull(LNK.StockID,0)
        When 0 Then AE.StockID
        Else LNK.StockID
    End StockID,
    IsNull(AE.LinkID,0) LinkID,
    IsNull(LNK.CapitalID,0) CapitalID,
    DE.SourceName,
    AE.SourceDate 'Date',
    IsNull(AE.Formula,'') Formula
From 
    AE_RevenuData AE
Left Join 
    Linking LNK With(NoLock) ON LNK.LinkID = AE.LinkID
Inner Join 
    DE_DataEntities DE ON DE.EntityID = AE.EntityID
Inner Join 
    DataEntityIDs TE ON TE.EntityID = DE.EntityID
Inner Join 
    STG_LockedEntityData STG ON STG.StockID = IsNull(LNK.StockID, AE.StockID) AND STG.CapitalID = IsNull(LNK.CapitalID, 0) 
Left Join 
    LKP_PrivateSource PS ON PS.PSourceId = AE.PSourceID
Where 
    AE.ProjectID IN (13)
    AND AE.LinkID IS NOT NULL

请看下面的计划:

  |--Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [Expr1026]=(0) THEN [MYDBNAME].[dbo].[AE_RevenueData].[StockID] as [AE].[StockID] ELSE [MYDBNAME].[dbo].[INV_InvestorFundLinking].[StockID] as [LNK].[StockID] END))
   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Expr1027]))
        |--Filter(WHERE:([MYDBNAME].[dbo].[STG_LockedEntityData].[StockID] as [STG].[StockID]=isnull([MYDBNAME].[dbo].[INV_InvestorFundLinking].[StockID] as [LNK].[StockID],[MYDBNAME].[dbo].[AE_RevenueData].[StockID] as [AE].[StockID]) AND [MYDBNAME].[dbo].[STG_LockedEntityData].[CapitalID] as [STG].[CapitalID]=[Expr1019]))
        |    |--Compute Scalar(DEFINE:([Expr1019]=isnull([MYDBNAME].[dbo].[INV_InvestorFundLinking].[CapitalID] as [LNK].[CapitalID],(0)), [Expr1026]=isnull([MYDBNAME].[dbo].[INV_InvestorFundLinking].[StockID] as [LNK].[StockID],(0))))
        |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([AE].[LinkID]))
        |              |--Nested Loops(Inner Join, WHERE:([MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID]=[MYDBNAME].[dbo].[STG_LockedEntityData].[LinkID] as [STG].[LinkID]))
        |              |    |--Sort(ORDER BY:([Expr1027] ASC))
        |              |    |    |--Hash Match(Inner Join, HASH:([TE].[EntityID])=([AE].[EntityID]))
        |              |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1008], [DE].[ListingID]))
        |              |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TE].[EntityID]))
        |              |    |         |    |    |--Table Scan(OBJECT:([MYDBNAME].[dbo].[DataEntityIDs] AS [TE]))
        |              |    |         |    |    |--Index Seek(OBJECT:([MYDBNAME].[dbo].[DE_DataEntities].[PK_DE_DataSources] AS [DE]), SEEK:([DE].[EntityID]=[MYDBNAME].[dbo].[DataEntityIDs].[EntityID] as [TE].[EntityID]) ORDERED FORWARD)
        |              |    |         |    |--Clustered Index Seek(OBJECT:([MYDBNAME].[dbo].[DE_DataEntities].[IX_DE_DataSources] AS [DE]), SEEK:([DE].[ListingID]=[MYDBNAME].[dbo].[DE_DataEntities].[ListingID] as [DE].[ListingID] AND [Uniq1008]=[Uniq1008]) LOOKUP ORDERED FORWARD)
        |              |    |         |--Compute Scalar(DEFINE:([Expr1018]=[MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID], [Expr1020]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[Formula] as [AE].[Formula],''), [Expr1021]=CONVERT(bit,[MYDBNAME].[dbo].[AE_RevenueData].[IsSumOfFunds] as [AE].[IsSumOfFunds],0), [Expr1022]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[ClientSpecificSource] as [AE].[ClientSpecificSource],N''), [Expr1023]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[GenericSource] as [AE].[GenericSource],N''), [Expr1027]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[PSourceID] as [AE].[PSourceID],(0))))
        |              |    |              |--Clustered Index Seek(OBJECT:([MYDBNAME].[dbo].[AE_RevenueData].[IX_AE_RevenueData] AS [AE]), SEEK:([AE].[ProjectID]=(13)),  WHERE:([MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID] IS NOT NULL) ORDERED FORWARD)
        |              |    |--Table Scan(OBJECT:([MYDBNAME].[dbo].[STG_LockedEntityData] AS [STG]))
        |              |--Index Seek(OBJECT:([MYDBNAME].[dbo].[INV_InvestorFundLinking].[PK_Linking_1] AS [LNK]), SEEK:([LNK].[LinkID]=[MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID]) ORDERED FORWARD)
        |--Clustered Index Seek(OBJECT:([MYDBNAME].[dbo].[LKP_PrivateSource].[PK_LKP_FactsetSource] AS [PS]), SEEK:([PS].[PSourceID]=[Expr1027]) ORDERED FORWARD)

请参考以下计划，其中不包括 LKP_PrivateSource 的加入。

  |--Compute Scalar(DEFINE:([Expr1015]=CASE WHEN [Expr1024]=(0) THEN [MYDBNAME].[dbo].[AE_RevenueData].[StockID] as [AE].[StockID] ELSE [MYDBNAME].[dbo].[Linking].[StockID] as [LNK].[StockID] END))
   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Expr1027]))
       |--Filter(WHERE:([MYDBNAME].[dbo].[STG_LockedEntityData].[StockID] as [STG].[StockID]=isnull([MYDBNAME].[dbo].[Linking].[StockID] as [LNK].[StockID],[MYDBNAME].[dbo].[AE_RevenueData].[StockID] as [AE].[StockID]) AND [MYDBNAME].[dbo].[STG_LockedEntityData].[CapitalID] as [STG].[CapitalID]=[Expr1017]))
            |--Compute Scalar(DEFINE:([Expr1017]=isnull([MYDBNAME].[dbo].[Linking].[CapitalID] as [LNK].[CapitalID],(0)), [Expr1024]=isnull([MYDBNAME].[dbo].[Linking].[StockID] as [LNK].[StockID],(0))))
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([AE].[LinkID]))
                      |--Hash Match(Inner Join, HASH:([AE].[LinkID])=([STG].[LinkID]))
                      |    |--Hash Match(Inner Join, HASH:([TE].[EntityID])=([AE].[EntityID]))
                      |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1008], [DE].[ListingID]))
                      |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TE].[EntityID]))
                      |    |    |    |    |--Table Scan(OBJECT:([MYDBNAME].[dbo].[DataEntityIDs] AS [TE]))
                      |    |    |    |    |--Index Seek(OBJECT:([MYDBNAME].[dbo].[DE_DataEntities].[PK_DE_DataSources] AS [DE]), SEEK:([DE].[EntityID]=[MYDBNAME].[dbo].[DataEntityIDs].[EntityID] as [TE].[EntityID]) ORDERED FORWARD)
                      |    |    |    |--Clustered Index Seek(OBJECT:([MYDBNAME].[dbo].[DE_DataEntities].[IX_DE_DataSources] AS [DE]), SEEK:([DE].[ListingID]=[MYDBNAME].[dbo].[DE_DataEntities].[ListingID] as [DE].[ListingID] AND [Uniq1008]=[Uniq1008]) LOOKUP ORDERED FORWARD)
                      |    |    |--Compute Scalar(DEFINE:([Expr1016]=[MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID], [Expr1018]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[Formula] as [AE].[Formula],''), [Expr1019]=CONVERT(bit,[MYDBNAME].[dbo].[AE_RevenueData].[IsSumOfFunds] as [AE].[IsSumOfFunds],0), [Expr1020]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[ClientSpecificSource] as [AE].[ClientSpecificSource],N''), [Expr1021]=isnull([MYDBNAME].[dbo].[AE_RevenueData].[GenericSource] as [AE].[GenericSource],N'')))
                      |    |         |--Clustered Index Seek(OBJECT:([MYDBNAME].[dbo].[AE_RevenueData].[IX_AE_RevenueData] AS [AE]), SEEK:([AE].[ProjectID]=(13)),  WHERE:([MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID] IS NOT NULL) ORDERED FORWARD)
                      |    |--Table Scan(OBJECT:([MYDBNAME].[dbo].[STG_LockedEntityData] AS [STG]))
                      |--Index Seek(OBJECT:([MYDBNAME].[dbo].[Linking].[PK_Linking_1] AS [LNK]), SEEK:([LNK].[LinkID]=[MYDBNAME].[dbo].[AE_RevenueData].[LinkID] as [AE].[LinkID]) ORDERED FORWARD)

- user899055

---

如果您还没有这样做，以下是您应该检查的几个事项：(a) 过时的统计信息，(b) 统计样本大小过小，(c) 索引碎片化。 - Luke Girvin

所有的外键列都被索引了吗？这适用于所有的表。 - marc_s

@marc_s：好的，我会更改并注意它...顺便说一句，即使删除IsNull，这也不是问题的原因。 - user899055

@Quassnoi：我更关注那些非常慢的“嵌套循环”连接——可能是因为一些外键列没有建立索引... - marc_s

有人能确认一下，STG_LockedEntityData表上的无索引可能是问题所在吗？ - user899055

Answer 1

由于某些原因，将LKP_PrivateSource添加一个LEFT JOIN会让优化器使用NESTED LOOPS而不是HASH JOIN来连接较早的STG_LockedEntityData表。

很难确定为什么会出现这种情况，但要改善嵌套循环，请在STG_LockedEntityData (stock_id, capital_id)上创建一个索引。

- Quassnoi