如何在SQL Server中预编译存储过程？

Question

有没有办法在SQL Server中预编译存储过程？我的要求是这样的... 我有一些存储过程，编译所需时间比执行更长。因此，我想预编译所有的存储过程。最好在数据库服务器启动和运行时预编译它们。如果您有任何想法，将不胜感激！

- fr21

---

你确定你没有在微观优化一些不是真正需要的东西吗？我从未在现实世界中看到过这样的需求。 - Sam Saffron

我有一个拥有成千上万条记录的表。所以我启用了索引视图来更快地访问数据。然而，这会影响到对该表进行插入和更新操作。因此，我的存储过程负责插入操作时需要很长时间才能执行完毕（第一次执行）。但之后便会变得更快。我发现存储过程在编译和准备执行计划时需要耗费时间。一旦它被编译，插入记录就足够快了。我是否能够通过预编译使其第一次就有如此高的性能呢？ - fr21

你是否真的在一个单独的表上有一个索引视图？如果是，则在该表上创建索引，而不要创建索引视图。 - Mathias F

Answer 1

编写一个脚本，使用“SET FMTONLY ON”执行每个存储过程。

该过程将被编译，但在执行过程中不会对数据库进行永久更改。无法在使用临时表（#table语法）的过程中使用此选项。

这是微软在Visual Studio中确定存储过程输出的方式。

- Carter Medlin

---

不是这样的。它使用 SET FMTONLY ON。 - Martin Smith

修改为使用“SET FMTONLY ON”解决方案。好主意，感谢评论和-1。 - Carter Medlin

Answer 2

您可以强制重新编译，但是直到下一次运行它才会发生。执行SP_RECOMPILE YourProcedureName。更多信息请参见此处...。每次运行都强制重新编译：
CREATE PROCEDURE YourProcedureName WITH RECOMPILE ......强制它在此时重新编译：
EXEC YourProcedureName WITH RECOMPILE。这是一篇关于优化SQL Server存储过程以避免重新编译的好文章。还有另一篇文章...。 根据OP的评论进行编辑： 为什么不手动运行它（在应用程序外部）使用虚假数据（不要太虚假，以至于执行计划是错误的，可以谷歌：sql server参数欺骗和嗅探）第一次运行，这可以强制编译，然后运行一些SQL来删除插入的内容。当用户第一次运行它时，它已经运行并且已经被编译。

- KM.

---

我尝试使用sp_Recompile选项。但是当第一次执行存储过程时，它需要很长时间，之后就足够快了。如何改进第一次使用的性能？ - fr21

Answer 3

是否可能只执行存储过程而不影响任何数据？如果是，那么您可能可以找到一种在服务器启动时触发此存储过程的方法。

- Paweł Czopowik

Answer 4

如果您正在使用SQL Server 2008，则可以使用计划指南来强制重用现有的预编译执行计划。

请参见了解计划指南，了解更多详细信息，特别是阅读“对象计划指南”。

我怀疑您的问题源于存储过程中实施的流程逻辑，并建议将其作为性能调整的第一点进行审查。

- John Sansom

---

嗨，我正在使用Sql Server 2005，在存储过程中包含一个简单的插入语句。表上存在索引视图。因此，准备执行计划需要消耗时间。有什么想法可以提前准备所有这些并保持sp准备好执行？ - fr21

你好，为了帮助我更好地理解你的环境，想请问一下你为什么使用索引视图而不是直接在基础表上添加索引？你使用了多少个索引视图？表上有多少个索引？ - John Sansom

这真的听起来像是个问题。索引视图经常被误解为仅仅是另一个索引，但它们实际上创建了视图的物理表示。如果你插入一个属于索引视图的表，那么你就会惹上麻烦。如果这是一个常规的导入程序的一部分，那么在导入之前禁用索引，在导入后重新启用索引。 - Mathias F

Answer 5

存储过程应该只在创建和首次执行时编译（以及仅创建查询计划）。

如果您经常使用WITH RECOMPILE，那么您应该停止使用。如果您这样做是为了强制重新计算查询计划，因为不同的参数与不同的查询计划一起更有效（并且如果这在性能方面很重要），则需要考虑为不同的查询计划创建不同的SP，可能还需要一个“父”SP来决定调用哪个。但这并不是一个无痛的过程。

如果您的表实际上处于小于百万行的范畴，那么我会特别注意索引和保持统计数据最新，并在空闲时间定期重新编译以保持查询计划的高效性。一旦您进入数千万或数亿行的范畴，那么可能有理由经历复制的痛苦。

- Mike Woodhouse

Answer 6

如今编译时间很少是重要的。将此代码插入程序顶部，亲自体验一下编译所需的时间是多么短。

SET STATISTICS TIME ON
GO

你会发现编译时间通常以0ms的形式给出，这意味着太小而不值得关注，而执行时间则在几十、几百甚至几千毫秒之间。

- David F Mayer

---

无论你是否正确，如果你不打算回答提问者特定的问题，那么应该在评论中发表观点，而不是作为答案。 - Ansel Santosa