在SQL Server中，如何允许SP中的单个查询有多个执行计划，而不必每次都重新编译？
在 SQL Server 中，允许 SP 中的查询存在多个执行计划而不必每次都重新编译的最佳方法是什么？
例如，在我的情况下，查询计划会根据查询使用的临时表中的行数而显着变化。由于没有令人满意的“一刀切”计划，而且每次都重新编译是不可接受的，所以我最终在几个 IF 语句中多次复制/粘贴（ick）SP 中的主查询，迫使 SQL引擎为每种情况提供自己的最佳计划。实际上，它在性能方面似乎工作得很好，但感觉有点笨重。 （我知道我可以类似地将这部分分解为多个 SP 来完成相同的操作。）有更好的方法吗？
IF @RowCount < 1
[paste query here]
ELSE IF @RowCount < 50
[paste query here]
ELSE IF @RowCount < 200
[paste query here]
ELSE
[paste query here]
In SQL Server, what is the best way to allow for multiple execution plans to exist for a query in a SP without having to recompile every time?
For example, I have a case where the query plan varies significantly depending on how many rows are in a temp table that the query uses. Since there was no "one size fits all" plan that was satisfactory, and since it was unacceptable to recompile every time, I ended up copy/pasting (ick) the main query in the SP multiple times within several IF statements, forcing the SQL engine to give each case its own optimal plan. It actually seemed to work beautifully performance-wise, but it feels a bit clunky. (I know I could similarly break this part out into multiple SPs to do the same thing.) Is there a better way to do this?
IF @RowCount < 1
[paste query here]
ELSE IF @RowCount < 50
[paste query here]
ELSE IF @RowCount < 200
[paste query here]
ELSE
[paste query here]
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在某些情况下，您可以使用OPTIMIZE FOR来创建针对以下目标的计划：参数的某个值（但本身不是多个计划）。这允许您指定我们希望 SQL Server 在创建执行计划时使用的参数值。这是 SQL Server 2005 及以上版本的提示。
使用 SQL Server 中的 OPTIMIZE FOR 提示优化参数驱动的查询
还有OPTIMIZE FOR UNKNOWN – SQL Server 2008 及以后的功能（明智地使用）：
也许还可以考虑优化临时工作负载选项
You can use OPTIMIZE FOR in certain situations, to create a plan targeted to a certain value of a parameter (but not multiple plans per se). This allows you to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 onwards hint.
Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint in SQL Server
There is also OPTIMIZE FOR UNKNOWN – a SQL Server 2008 onwards feature (use judiciously):
Perhaps also look into optimize for ad hoc workloads Option
SQL Server 2005+ 具有语句级重新编译，并且更擅长处理此类分支。您仍然有一个计划，但该计划可以在语句级别部分重新编译。
但它很丑。
我个人会选择@Mitch Wheat 的选项，因为无论如何您都需要使用临时表对存储过程进行重新编译。请参阅临时表和存储过程编译
SQL Server 2005+ has statement level recompilation and is better at dealing with this kind of branching. You have one plan still but the plan can be partially recompiled at the statement level.
But it is ugly.
I'd go with @Mitch Wheat's option personally because you have recompilations anyway with the stored procedure using a temp table. See Temp table and stored proc compilation