选项（RECOMPILE）总是更快;为什么？ [英] 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.

这是通过查询分析器或从我的C＃程序通过 SqlCommand.ExecuteReader（）。调用（或不调用） 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'

，参数通过 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根据数据库中的数据尝试确定此查询的最有效的执行计划以及传入的参数（参数嗅探），然后缓存此计划。这意味着如果您创建的查询在您的数据库中有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.

这篇关于选项（RECOMPILE）总是更快;为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！