CODEWITHSUNDEEP
sql-serverstored-procedures
webworm
webworm

Reputation: 11019

SQL Server - Stored procedures slow vs "Giant" script

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.

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.

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.

Upvotes: 1

Views: 143

Answers (1)

TomTom
TomTom

Reputation: 62127

Any ideas why this would happen?

Compilation.

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

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.

Upvotes: 1

Related Questions