SQL Server Performance Forum – Threads Archive

Executing large SPs

I have several SP’s need to be exexuted one after other. Each of these SP is taking around 1 hour. Instead of waiting for each of them to complete and start the next one, i was planning to put all of them in a single sp and execute at once. My friends feel that this may block server resources till the whole process completes. Now i am planning to execute all the sp’s in QA with a GO in between. execute sp1
GO
execute sp2
GO
execute sp3
GO
execute sp4
GO
So that each will execute in its own batch. Is this the correct approach? Or is there a better way?
Thank you.
I think, therefore I am

Few things about stored procedure:
– Stored procedure maintenance becomes easier because we can change one stored procedure and it immediately becomes effective for all users and programs.
– Stored procedures are more efficient than SQL statements, when executed from a program. The reason why this is true is that when the stored procedure is created and saved it is compiled.
– During the compilation process, SQL Server creates and optimizes an execution plan for the stored procedure. Also, once a stored procedure is executed, it is placed in SQL Server cache. This means that subsequent executions are executed from cache, which also provides improved performance. So first of all you must asses how long each stored procedure is taking time to complete the execution and what kind of performance is affected during this execution. If those are minimal execution then test the execution plan in query analyzer and ensure to recompile the involved SPs to get optimum performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also if the SP’s are dependent on each other for execution I would recommend hadling error returned from the Sp’s to determine if the next bach needs to be run. In this case you can’t use GO syntax. The SP’s are going to take as much time as they need irrespective of the fact if they are in a single batch or a different batch. There is nothing like blocking the server as they will take the same resorces in either method. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

]]>