文章/答案/技术大牛

发布

社区首页 >问答首页 >Server 2008:处理特定日期边界时查询性能非常慢

问Server 2008:处理特定日期边界时查询性能非常慢
EN

Stack Overflow用户

提问于 2009-11-16 17:36:16

回答 3查看 3.3K关注 0票数 2

对，这是个奇怪的案子。

我们有一个web服务，它将数据返回给silverlight客户端。查询是使用实体框架针对Server 2008数据库生成的。例如，大多数这些查询都是基于日期范围的，从这个日期到那个日期之间的拉出结果。此外，视图被用来使事情变得更容易一些。

我们已经注意到，当发出一组在某一日期或之后开始的特定查询时，执行将非常缓慢。这一天是任何一年的11月5日。如果我们的开始日期是一天前，执行将是即时的。所以，第五至第六，慢点。4-6-快。

下面是EF生成的SQL：

这个查询非常慢(30秒)

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [Name], 
[GroupBy1].[A1] AS [C2]
FROM ( SELECT 
[Extent1].[Name] AS [K1], 
SUM([Extent1].[Value]) AS [A1]
FROM (SELECT 
  [view_answers].[Value] AS [Value], 
  [view_answers].[Comment] AS [Comment], 
  [view_answers].[NewStockist] AS [NewStockist], 
  [view_answers].[NewDistPoint] AS [NewDistPoint], 
  [view_answers].[VoucherUsed] AS [VoucherUsed], 
  [view_answers].[CashTotal] AS [CashTotal], 
  [view_answers].[AnswerType] AS [AnswerType], 
  [view_answers].[StartTime] AS [StartTime], 
  [view_answers].[ActualEndTime] AS [ActualEndTime], 
  [view_answers].[Complete] AS [Complete], 
  [view_answers].[UserID] AS [UserID], 
  [view_answers].[UserName] AS [UserName], 
  [view_answers].[QuestionType] AS [QuestionType], 
  [view_answers].[ProductSKU] AS [ProductSKU], 
  [view_answers].[BrandID] AS [BrandID], 
  [view_answers].[TeamID] AS [TeamID], 
  [view_answers].[Name] AS [Name], 
  [view_answers].[Stage] AS [Stage], 
  [view_answers].[Question] AS [Question]
  FROM [dbo].[view_answers] AS [view_answers]) AS [Extent1]
  WHERE 
     ([Extent1].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D') 
     AND (N'Distribution Points' = [Extent1].[QuestionType]) 
     AND ([Extent1].[StartTime] >= '11/05/2009 00:00:00') 
     AND ([Extent1].[StartTime] <= '11/08/2009 00:00:00') 
     AND (1 = [Extent1].[Complete]) 
     AND (2 = [Extent1].[BrandID]) 
     AND (N'Distribution Points' = [Extent1].[QuestionType])
  GROUP BY 
     [Extent1].[Name])  
  AS [GroupBy1]

，这个会快得多

    SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Name], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
    [Extent1].[Name] AS [K1], 
    SUM([Extent1].[Value]) AS [A1]
    FROM (SELECT 
      [view_answers].[Value] AS [Value], 
      [view_answers].[Comment] AS [Comment], 
      [view_answers].[NewStockist] AS [NewStockist], 
      [view_answers].[NewDistPoint] AS [NewDistPoint], 
      [view_answers].[VoucherUsed] AS [VoucherUsed], 
      [view_answers].[CashTotal] AS [CashTotal], 
      [view_answers].[AnswerType] AS [AnswerType], 
      [view_answers].[StartTime] AS [StartTime], 
      [view_answers].[ActualEndTime] AS [ActualEndTime], 
      [view_answers].[Complete] AS [Complete], 
      [view_answers].[UserID] AS [UserID], 
      [view_answers].[UserName] AS [UserName], 
      [view_answers].[QuestionType] AS [QuestionType], 
      [view_answers].[ProductSKU] AS [ProductSKU], 
      [view_answers].[BrandID] AS [BrandID], 
      [view_answers].[TeamID] AS [TeamID], 
      [view_answers].[Name] AS [Name], 
      [view_answers].[Stage] AS [Stage], 
      [view_answers].[Question] AS [Question]
      FROM [dbo].[view_answers] AS [view_answers]) AS [Extent1]
    WHERE 
       ([Extent1].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D') 
       AND (N'Distribution Points' = [Extent1].[QuestionType]) 
       AND ([Extent1].[StartTime] >= '11/04/2009 00:00:00') 
       AND ([Extent1].[StartTime] <= '11/08/2009 00:00:00') 
       AND (1 = [Extent1].[Complete]) 
       AND (2 = [Extent1].[BrandID]) 
       AND (N'Distribution Points' = [Extent1].[QuestionType])
    GROUP BY 
       [Extent1].[Name])  
    AS [GroupBy1]

如果我们把开始日期设置为去年的11月5日，那么执行速度会很慢，去年的11月4日，它的执行速度也会很快。从数据库中的数据来看，5号前后没有什么不寻常之处。此外，似乎在第五次开始日期之后的查询将运行缓慢。

我被困住了！

(数据库是远程托管的，因此我无法直接访问它)

更新

谢谢你们的回答。首先，我可能应该清楚地表明，我对sql server的了解是肤浅的。我构建不同质量偏差的数据库，然后使用类似Linq到SQL或EF之类的方法来处理它们。所以我觉得有点不太深入了。

关于联接，我正在查询的视图确实包含了大约6-7个表中的数据。下一次工作时，我将尝试获取一些数据，并在这里添加一些更多的信息。我对执行计划知之甚少，或者它们是我可以通过看到的东西？

从慢速查询(受影响的3行)更新统计数据

Table 'tblProducts'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestionTypes'. Scan count 0, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestions'. Scan count 0, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblSessions'. Scan count 0, logical reads 28551, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblAnswers'. Scan count 1, logical reads 1976256, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCalls'. Scan count 1, logical reads 439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUsers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

来自快速查询的统计信息

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblAnswers'. Scan count 1, logical reads 7008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblProducts'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestions'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestionTypes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCalls'. Scan count 1, logical reads 439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblSessions'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUsers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

UPDATE查询执行计划(对我来说是一件新的事情)是建议我在一个表中的一个表中添加一个新的索引--表中的query。建议的sql如下：

USE [brandfourcoke]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblAnswers] ([QuestionID])
INCLUDE ([CallID],[Value])
GO

查询成本的估计改进为93%。我该继续做这个吗？该数据库处于活动环境中，使用了server 2008的更改跟踪和同步框架。因此，我总是担心数据库的更改会影响跟踪的数据，并破坏我的客户端。添加索引重置更改跟踪数据吗？谢谢你们的帮助。我觉得自己是个完全的初学者。

sql-server

entity-framework

sql-server-2008

回答 3

Stack Overflow用户

回答已采纳

发布于 2009-11-16 18:00:10

使用SET sTATISTICS IO ON运行缓慢和快速的查询，看看两者之间的逻辑读取/物理读取量是否存在显著差异。

最有可能的是，数据中存在强烈的倾斜。例如，在fast上的计划执行一个由10行结果驱动的嵌套循环(导致10个嵌套查找)，而慢速的计划突然从上一次看到10的位置看到10000行，从而产生10000次查找。尽管您的查询没有联接，但引擎可以使用各种访问索引，并将索引与群集索引连接起来。实际的执行计划总是能准确地揭示到底发生了什么。

票数 5

Stack Overflow用户

发布于 2009-11-16 17:58:47

在不了解任何其他信息的情况下，首先猜测您可能是特定操作的索引使用情况的触到临界点，或者对于给定的列有过时/错误的统计分布信息。可能还有其他一些问题，比如次优索引(可能是错误的，可能键排序不正确，等等)，考虑到您使用的是Sql 2008，您确定没有在特定列上创建过滤索引，而不是传统的完整索引，等等--但是，为了确定这一点，我们需要看到更多的信息(例如模式、索引、查询计划、数据分布、统计数据等等)。

如果您可以发布上面列出的每个查询所使用的查询计划，这可能会有所帮助，这至少可以帮助我们确定您是否得到了截然不同的计划。

票数 4

Stack Overflow用户

发布于 2009-11-16 18:30:50

这个查询是为什么使用生成的sql常常是一个糟糕的想法的原因之一。据我所知，如果您自己编写的话，这是一个等价的查询：

     SELECT 1 AS [C1],     
            [view_answers].[Name] AS [K1],     
            SUM([view_answers].[Value]) AS [C2]   
     FROM  [view_answers] 
     WHERE ([view_answers].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D')        
     AND (N'Distribution Points' = [view_answers].[QuestionType])        
     AND ([view_answers].[StartTime] >= '11/04/2009 00:00:00')        
     AND ([view_answers].[StartTime] <= '11/08/2009 00:00:00')        
     AND (1 = [view_answers].[Complete])        
     AND (2 = [view_answers].[BrandID])        
     AND (N'Distribution Points' = [view_answers].[QuestionType])    
     GROUP BY        [view_answers].[Name]

尝试这两个日期，看看在使用11月5日时是否得到相同的结果和相同的延迟。哪些字段是索引的？

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/1743679

复制

相似问题

问Server 2008:处理特定日期边界时查询性能非常慢
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server 2008:处理特定日期边界时查询性能非常慢EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server 2008:处理特定日期边界时查询性能非常慢
EN