SQL Server:在 proc 定义中使用“WITH RECOMPILE"的影响?

2021-12-20 00:00:00 tsql sql-server stored-procedures recompile

我对存储过程的 WITH RECOMPILE 选项的理解通常仅限于使用带有单个存储过程调用的子句作为尾随参数:

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 吗?还是只是在下一次更改 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).

这个提示通常用于处理大量数据和做复杂报告的procs，当整体查询时间很大，重建计划的时间与更好的计划节省的时间相比可以忽略不计.

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.

相关文章