选项(重新编译)总是更快；为什么? [英] OPTION (RECOMPILE) is Always Faster; Why?

问题描述

我遇到了一个奇怪的情况，将 OPTION (RECOMPILE) 附加到我的查询会导致它在半秒内运行，而省略它会导致查询花费超过五分钟.

I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes.

这是从查询分析器或通过 SqlCommand.ExecuteReader() 从我的 C# 程序执行查询的情况.调用(或不调用)DBCC FREEPROCCACHE 或 DBCC dropcleanbuffers 没有区别；查询结果总是在使用 OPTION (RECOMPILE) 时立即返回，如果没有它超过五分钟.始终使用相同的参数调用查询 [为了这个测试].

This is the case when the query is executed from Query Analyzer or from my C# program via SqlCommand.ExecuteReader(). Calling (or not calling) DBCC FREEPROCCACHE or DBCC dropcleanbuffers makes no difference; Query results are always returned instantaneously with OPTION (RECOMPILE) and greater than five minutes without it. The query is always called with the same parameters [for the sake of this test].

我使用的是 SQL Server 2008.

I'm using SQL Server 2008.

我对编写 SQL 相当熟悉，但以前从未在查询中使用过 OPTION 命令，并且在扫描本论坛上的帖子之前不熟悉计划缓存的整个概念.我从帖子中了解到 OPTION (RECOMPILE) 是一个昂贵的操作.它显然为查询创建了一个新的查找策略.那么，为什么省略 OPTION (RECOMPILE) 的后续查询如此缓慢?后续查询不应该使用在包含重新编译提示的先前调用中计算的查找策略吗?

I'm fairly comfortable with writing SQL but have never used an OPTION command in a query before and was unfamiliar with the whole concept of plan caches until scanning the posts on this forum. My understanding from the posts is that OPTION (RECOMPILE) is an expensive operation. It apparently creates a new lookup strategy for the query. So why is it then, that subsequent queries that omit the OPTION (RECOMPILE) are so slow? Shouldn't the subsequent queries be making use of the lookup strategy that was computed on the previous call which included the recompilation hint?

每次调用都需要重新编译提示的查询是否非常罕见?

Is it highly unusual to have a query that requires a recompilation hint on every single call?

抱歉这个入门级的问题，但我真的无法对此做出正面或反面.

Sorry for the entry-level question but I can't really make heads or tails of this.

更新:我被要求发布查询...

select acctNo,min(date) earliestDate 
from( 
    select acctNo,tradeDate as date 
    from datafeed_trans 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_money 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_jnl 
    where feedid=@feedID and feedDate=@feedDate 
)t1 
group by t1.acctNo
OPTION(RECOMPILE)

从查询分析器运行测试时，我在前面添加了以下几行:

When running the test from Query Analyzer, I prepend the following lines:

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

从我的 C# 程序调用它时，参数通过 SqlCommand.Parameters 属性传入.

When calling it from my C# program, the parameters are passed in via the SqlCommand.Parameters property.

出于本次讨论的目的，您可以假设参数永远不会改变，因此我们可以排除次优参数气味是原因.

For the purposes of this discussion, you can assume that the parameters never change so we can rule out sub-optimal parameter smelling as the cause.

推荐答案

有时使用 OPTION(RECOMPILE) 是有意义的.根据我的经验，唯一可行的选择是在您使用动态 SQL 时.在您探索这对您的情况是否有意义之前，我建议您重建您的统计数据.这可以通过运行以下命令来完成:

There are times that using OPTION(RECOMPILE) makes sense. In my experience the only time this is a viable option is when you are using dynamic SQL. Before you explore whether this makes sense in your situation I would recommend rebuilding your statistics. This can be done by running the following:

EXEC sp_updatestats

然后重新创建您的执行计划.这将确保在创建执行计划时将使用最新信息.

And then recreating your execution plan. This will ensure that when your execution plan is created it will be using the latest information.

添加 OPTION(RECOMPILE) 会在每次查询执行时重建执行计划.我从未听说过将创建新的查找策略描述为，但也许我们只是对同一件事使用不同的术语.

Adding OPTION(RECOMPILE) rebuilds the execution plan every time that your query executes. I have never heard that described as creates a new lookup strategy but maybe we are just using different terms for the same thing.

创建存储过程时(我怀疑您是从 .NET 调用 ad-hoc sql 但 如果您使用的是参数化查询，那么这最终会成为一个存储过程调用) SQL Server 尝试根据您数据库中的数据确定此查询的最有效执行计划以及传入的参数 (parameter嗅探)，然后缓存这个计划.这意味着，如果您在数据库中有 10 条记录的情况下创建查询，然后在有 100,000,000 条记录时执行该查询，则缓存的执行计划可能不再是最有效的.

When a stored procedure is created (I suspect you are calling ad-hoc sql from .NET but if you are using a parameterized query then this ends up being a stored proc call) SQL Server attempts to determine the most effective execution plan for this query based on the data in your database and the parameters passed in (parameter sniffing), and then caches this plan. This means that if you create the query where there are 10 records in your database and then execute it when there are 100,000,000 records the cached execution plan may no longer be the most effective.

总而言之 - 我认为 OPTION(RECOMPILE) 在这里没有任何好处.我怀疑你只需要更新你的统计数据和你的执行计划.根据您的情况，重建统计信息可能是 DBA 工作的重要组成部分.如果您在更新统计信息后仍然遇到问题，我建议您同时发布两个执行计划.

In summary - I don't see any reason that OPTION(RECOMPILE) would be a benefit here. I suspect you just need to update your statistics and your execution plan. Rebuilding statistics can be an essential part of DBA work depending on your situation. If you are still having problems after updating your stats, I would suggest posting both execution plans.

并且回答您的问题 - 是的，我想说，每次执行查询时重新编译执行计划的最佳选择是非常不寻常的.

And to answer your question - yes, I would say it is highly unusual for your best option to be recompiling the execution plan every time you execute the query.

这篇关于选项(重新编译)总是更快；为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！