SQL Server - 存储过程慢与“巨型"脚本 [英] SQL Server - Stored procedures slow vs "Giant" script

问题描述

我有大量的存储过程(大约 200 个)需要顺序执行.理想情况下，我想创建一个主"存储过程，它将一个接一个地执行每个单独的存储过程.

I have a large number of stored procedures (about 200) that need to be executed sequentially. Ideally I wanted to create a single "master" stored procedure that would execute each of the individual stored procedures one after another.

但是，当我执行主存储过程时，它在运行很长时间后始终冻结.话虽如此，如果我从 200 个单独的存储过程中提取所有 SQL 代码并创建一个巨大的 SQL 脚本文件，它就可以毫无问题地运行.

However, when I execute the master stored procedure it consistently freezes after running a long time. That being said, if I take all the SQL code from the 200 individual stored procedures and create one giant SQL script file, it runs without any issue.

SQL 代码查询单独的表并将数据的子集插入到主汇总"表中.

The SQL code queries separate tables and inserts a subset of the data into a master "summary" table.

任何想法为什么会发生这种情况?是否有一些需要更多内存的存储过程?我更愿意将所有内容都保存在存储过程中，以便我们可以更轻松地管理安全性和更新.

Any ideas why this would happen? Is there something about stored procedures that take more memory? I would prefer to keep everything in stored procedures so we could manage security and updates easier.

推荐答案

知道为什么会发生这种情况吗?

Any ideas why this would happen?

编译.

可能使用此时有效的统计数据逐批编译主脚本.

The master script likely is compiled batch by batch using the statistics valid at this point.

SP 将在开始时编译一次，如果在运行期间统计数据发生变化 - 这是一系列加载的典型情况 - 就可以了.尤其是在处理过程中统计变化显着的情况下.基本上，与某些表的运行时统计数据相比，开始时的统计数据 - 编译时 - 完全不同.

The SP will be compiled once at start, and if the statistics change during the run - as is typial for a sequence of loads - there you go. Especially if the statistical change is significant during processing. Basically the stats at teh beginning - when things are compiled - are totally off compared to the runtime stats for some tables.

有一个重新编译选项，您可以设置单个语句来避免这种情况.

There is a recompile option that you can se tin the individual statements to avoid this.

这篇关于SQL Server - 存储过程慢与“巨型"脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！