SQL Server 2005 查询性能

Question

为什么这个操作需要4秒才能执行（使用2000000行数据）？

DECLARE @AccountId INT 
DECLARE @Max INT 
DECLARE @MailingListId INT 

SET @AccountId = 6730
SET @Max = 2000
SET @MailingListId = 82924

SELECT TOP (@Max) anp_Subscriber.Id , Name, Email 
FROM anp_Subscription WITH(NOLOCK) 
  INNER JOIN anp_Subscriber WITH(NOLOCK) 
    ON anp_Subscriber.Id = anp_Subscription.SubscriberId
WHERE [MailingListId] = @MailingListId 
  AND Name LIKE '%joe%' 
  AND [AccountID] = @AccountId

---

--------------------- 这个查询执行时间不到1秒（查询200万行） -----------------------

SELECT TOP 2000 anp_Subscriber.Id ,Name, Email 
FROM anp_Subscription WITH(NOLOCK) 
  INNER JOIN anp_Subscriber WITH(NOLOCK)
    ON anp_Subscriber.Id = anp_Subscription.SubscriberId
WHERE [MailingListId] = 82924 
  AND Name LIKE '%joe%' 
  AND [AccountID] = 6730

---

为什么执行时间会有差异？我想使用上方的查询语句。我能做些什么来优化它吗？

提前感谢！/Christian

- Christian V

---

我认为这与此相关：https://dev59.com/9kbRa4cB1Zd3GeqPxiWc - ypercubeᵀᴹ

以及MSDN文章：http://technet.microsoft.com/en-gb/library/cc966425.aspx#E6TAE - ypercubeᵀᴹ

在第一种情况下，SQL Server 必须针对这些参数的任何可能值来优化查询。而在第二种情况下，由于实际的字面值已知，查询优化器可以选择更合适的查询计划——因为它只需要针对特定的值进行优化即可。 - marc_s

我能以某种方式更具体地指定参数吗？ - Christian V

你尝试过添加 OPTION (OPTIMIZE FOR (@Max = 2000)) 吗？ - GilM

SQL Server 查询优化团队博客 - Bogdan Sahlean

Answer 1

将 OPTION (RECOMPILE) 添加到查询的末尾。

SQL Server 不会“嗅探”变量的值，因此您将获得基于猜测统计数据而不是针对实际变量值定制的计划。

- Martin Smith

---

谢谢。这有助于提高性能。但是另一个问题：每次执行查询时重新编译对服务器要求很高吗？它可能会被频繁使用... - Christian V

@user1008621 - 如果你要不断重新编译相同的值，那么这似乎有点浪费时间，你可以尝试像GilM建议的那样使用OPTION (OPTIMIZE FOR。 - Martin Smith

我也是这么想的，但在这个查询中，我可以优化的唯一值是@Max，但由于某种原因，执行时间并没有得到改善... 你有什么想法为什么会这样？ - Christian V

@ChristianV - 不确定你的意思是你只能优化这个值。语法OPTION (OPTIMIZE FOR (@AccountId = 6730, @Max = 2000, @MailingListId = 82924))应该可以工作吧？你也可以查看计划之间的差异，并使用显式查询提示来获取所需的计划（例如，连接类型或访问方法）。 - Martin Smith

哦，我只是想说我不知道将要使用哪个accountId或mailingListId... 所以我不知道是否正确地为这些特定值优化查询？顺便问一下，你所说的访问方法是什么意思？ - Christian V

@ChristianV - 通过访问方法，我指的是例如您可以使用提示来强制使用特定索引。如果您不知道变量的值将是什么，并且根据实际变量值获得不同的计划，则最好只使用 OPTION (RECOMPILE)。您需要尝试并确定编译成本与使用不适当计划的成本*使用不适当计划的概率，以确定编译成本是否值得。 - Martin Smith

Answer 2

有一个可能需要检查的项目是表中的MailingListId和AccountId字段是否为INT类型。例如，如果类型为BIGINT，则查询优化器通常不会使用这些字段上的索引。当您明确定义值而不是使用变量时，这些值会隐式转换为正确的类型。

确保类型匹配。

- Brian Knight

Answer 3

第二个查询只需要处理2000条记录。就这样。

第一个查询必须处理所有记录才能找到最大值。

前2000条并不是最高的2000条，而是结果集中的前2000条 - 以任何顺序。

如果您想使它们相同，第二个查询应该写成

TOP 1

然后按anp_Subscriber.Id降序排序（加快首选项）。

- TomTom

---

我没有使用Max函数。也许将@Max用作变量名不太聪明... :/ - Christian V