何时使用WITH RECOMPILE选项的经验法则 [英] Rule of thumb on when to use WITH RECOMPILE option

问题描述

我知道WITH RECOMPILE选项会强制优化器为存储的proc重建查询计划，但是您希望什么时候发生呢?

I understand that the WITH RECOMPILE option forces the optimizer to rebuild the query plan for stored procs but when would you want that to happen?

何时使用WITH RECOMPILE选项以及何时不使用WITH RECOMPILE选项的经验法则是什么?

What are some rules of thumb on when to use the WITH RECOMPILE option and when not to?

仅将它放在每个sproc上的有效开销是多少?

What's the effective overhead associated with just putting it on every sproc?

推荐答案

就像其他人所说的那样，出于习惯的考虑，您不想在每个存储的proc中简单地包含WITH RECOMPILE.这样，您将消除存储过程的主要好处之一:它可以保存查询计划.

As others have said, you don't want to simply include WITH RECOMPILE in every stored proc as a matter of habit. By doing so, you'd be eliminating one of the primary benefits of stored procedures: the fact that it saves the query plan.

为什么这可能很重要?计算查询计划比编译常规过程代码要费很多力气.由于SQL语句的语法仅指定要要的内容，而没有(通常)如何进行指定，因此在创建物理数据库时，数据库具有很大的灵活性.规划(即逐步收集和修改数据的分步说明).数据库查询预处理器可以执行很多技巧"，并可以做出选择-联接表的顺序，使用的索引，在联接之前或之后应用WHERE子句等.

Why is that potentially a big deal? Computing a query plan is a lot more intensive than compiling regular procedural code. Because the syntax of a SQL statement only specifies what you want, and not (generally) how to get it, that allows the database a wide degree of flexibility when creating the physical plan (that is, the step-by-step instructions to actually gather and modify data). There are lots of "tricks" the database query pre-processor can do and choices it can make - what order to join the tables, which indexes to use, whether to apply WHERE clauses before or after joins, etc.

对于简单的SELECT语句，可能没有什么区别，但是对于任何非平凡的查询，数据库将花费一些严重的时间(以毫秒为单位，而不是通常的微秒)来得出一个最佳计划.对于非常复杂的查询，它甚至不能保证 optimized 计划，它只能使用启发式方法来提出 pretty good 计划.因此，通过迫使它每次都重新编译，就意味着它必须反复执行该过程，即使它之前得到的计划是非常好的.

For a simple SELECT statement, it might not make a difference, but for any non-trivial query, the database is going to spend some serious time (measured in milliseconds, as opposed to the usual microseconds) to come up with an optimal plan. For really complex queries, it can't even guarantee an optimal plan, it has to just use heuristics to come up with a pretty good plan. So by forcing it to recompile every time, you're telling it that it has to go through that process over and over again, even if the plan it got before was perfectly good.

取决于供应商，应该有用于重新编译查询计划的自动触发器-例如，如果表上的统计信息发生了显着变化(例如，某列中的值的直方图开始时分布均匀，则随着时间的推移变得高度偏斜) )，那么DB应该注意到这一点并重新编译该计划.但总的来说，数据库的实现者总体上将比您更聪明.

Depending on the vendor, there should be automatic triggers for recompiling query plans - for example, if the statistics on a table change significantly (like, the histogram of values in a certain column starts out evenly distributed by over time becomes highly skewed), then the DB should notice that and recompile the plan. But generally speaking, the implementers of a database are going to be smarter about that on the whole than you are.

与任何与性能相关的内容一样，请勿在黑暗中拍照；找出瓶颈，这些瓶颈会花费90％的性能，然后首先解决它们.

As with anything performance related, don't take shots in the dark; figure out where the bottlenecks are that are costing 90% of your performance, and solve them first.

这篇关于何时使用WITH RECOMPILE选项的经验法则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！