如何在 SQL Server 中预编译存储过程? [英] How to precompile stored procedures in SQL server?
问题描述
有没有办法在 SQL Server 中预编译存储过程?我的需求是这样的..我有一些存储过程，编译比执行花费更多的时间.所以我想预编译所有的存储过程.最好在 db 服务器启动和运行时预编译它们.
Is there any way to pre compile stored procedures in SQL Server? My requirement goes like this.. I have some stored procedures, which take more time in compiling than executing. So I want to precompile all the stored procedures. It would be nice to precompile them when the db server is started and running.
对此的任何想法都会非常有帮助！
Any ideas on this would be greatly helpful!
推荐答案
可以强制重新编译，但要等到下次运行时才会发生
you can force a recompile, but it will not happen until the next time it is run
EXEC SP_RECOMPILE YourProcedureName
每次运行时强制它重新编译:
创建程序您的程序名称与重新编译.....
force it to recompile each time it is run:
CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....
这次强制它重新编译:
EXEC YourProcedureName WITH RECOMPILE
force it to recompile this time:
EXEC YourProcedureName WITH RECOMPILE
这里有一篇关于优化 SQL Server 存储过程以避免的好文章重新编译
编辑基于 OP 的评论:
你为什么不第一次用伪造的数据(不是那么虚假以至于执行计划很糟糕，谷歌:sql server参数欺骗和嗅探)手动运行它(在应用程序之外)，这可以强制编译，然后运行一些 sql 删除插入的内容.用户第一次运行时，已经运行完毕，编译完成.
why don't you manually run it (outside the application) with bogus data (not so bogus that the execution plan is bad though, google: sql server parameter spoofing and sniffing) the first time, that can force a compile, then run some sql to delete out what was inserted. when the users run it for the first time, it will have already run and have been compiled.
这篇关于如何在 SQL Server 中预编译存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
