SQL Server：使用“WITH RECOMPILE”的效果在过程定义中？

发布于 2024-10-14 12:32:24 字数 471 浏览 2 评论 0 原文

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

exec sp_mystoredproc 'Parameter1', 2, '1/28/2011' with recompile

包含 WITH RECOMPILE 的效果是什么在实际的过程定义中？每次执行时都会重新编译过程吗？或者只是下次程序改变时？

例子：

CREATE PROCEDURE [dbo].[sp_mystoredproc]
    (@string1           varchar(8000)
    ,@int2              int = 2
    ,@dt_begin          DATETIME
    with recompile
AS
... proc code ...

原文

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

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?

Example:

CREATE PROCEDURE [dbo].[sp_mystoredproc]
    (@string1           varchar(8000)
    ,@int2              int = 2
    ,@dt_begin          DATETIME
    with recompile
AS
... proc code ...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

dawn曙光 2024-10-21 12:32:24

这使得进程每次运行时都会重建所有查询的计划。

过程参数的值会影响滤波器的选择性，这很有用。

比如说，此查询的最佳计划：

SELECT  *
FROM    orders
WHERE   order_date BETWEEN @begin_report AND @from_report

如果日期范围较大，则进行完整扫描；如果日期范围较小，则进行索引扫描。

使用 WITH RECOMPILE 创建，过程将在每次执行时构建计划；如果没有，它将坚持单一计划（但会节省重新编译本身的时间）。

此提示通常用在处理大量数据和执行复杂报告的过程中，此时总体查询时间很长，并且与更好的计划节省的时间相比，重建计划的时间可以忽略不计。

This makes the proc rebuild the plans of all queries every time it's run.

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.

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).

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.

回复收藏 0 原文

~没有更多了~