SQL Server:在proc定义中使用"WITH RECOMPILE"的影响? [英] SQL Server: Effects of using 'WITH RECOMPILE' in proc definition?
问题描述
我对存储过程的WITH RECOMPILE
选项的理解通常仅限于将带有单个存储proc调用的子句用作尾随参数:
My understanding of the WITH RECOMPILE
option with stored procedures is generally limited to using the clause with a single stored proc call as a trailing parameter:
exec sp_mystoredproc 'Parameter1', 2, '1/28/2011' with recompile
在实际的proc定义中包含WITH RECOMPILE
有什么影响?每次执行时,都会重新编译proc吗?还是下次修改程序?
What are the effects of including WITH RECOMPILE
in the actual proc definition? Does this recompile the proc every time it's executed? Or just the next time the proc is altered?
示例:
CREATE PROCEDURE [dbo].[sp_mystoredproc]
(@string1 varchar(8000)
,@int2 int = 2
,@dt_begin DATETIME
with recompile
AS
... proc code ...
推荐答案
这使proc每次运行时都重新构建所有查询的计划.
This makes the proc rebuild the plans of all queries every time it's run.
有用的proc参数值会影响过滤器的选择性.
Useful it the values of the proc parameters affect the filter selectivity.
说,此查询的最佳计划:
Say, the optimal plan for this query:
SELECT *
FROM orders
WHERE order_date BETWEEN @begin_report AND @from_report
如果日期范围较大,则
将是完整扫描,如果范围较小,则 将是索引扫描.
will be a full scan if the date range is large or an index scan if it's small.
使用WITH RECOMPILE
创建的proc将在每次执行时构建计划;没有一个,它将遵循一个计划(但是会节省重新编译本身的时间).
Created using WITH RECOMPILE
, the proc will build the plan on each execution; without one, it will stick to a single plan (but will save time on recompilation itself).
此提示通常用于处理大量数据并生成复杂报表的proc中,这是因为总体查询时间很大,并且与更好的计划节省的时间相比,重建计划的时间可以忽略不计.
This hint is usually used in procs processing large volumes of data and doing complex reports, when the overall query time is large and time for rebuilding the plan is negligible compared with the time saved by a better plan.
这篇关于SQL Server:在proc定义中使用"WITH RECOMPILE"的影响?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!