I have worked in a web based project, in my code sometimes i want to execute a stored procedure more than 1000 times in a single button click. The button is used for some type of approval process and when user clicks the button we approve many things for him, so as the part of process we want to run a stored procedure continuously more than 1000 times. Each time take data from a view and store as xml document then parse that document then add each document as command text then execute the stored procedure. But the problem is it takes long time. 3-4 minutes to take to complete the process . i am looking for improving the performance. Anybody has any suggestion please tell me .

i had already go through this link but still the execution lags

  • Check from plan cache that which step(s) are consuming the most time, that way you can focus on the actual issue, not the whole procedure. But just a guess, XML handling takes quite a lot of time.
    – James Z
    Feb 3 '15 at 10:21
  • I had check the execution plan, trace but i can't find something that takes more time. Feb 3 '15 at 10:24

In addition to Kapil's answer, I'd like to add the following...

SQL Server is optimized to perform set-based operations. Your stored procedure reads very much like an iterative process. (Do this step, increment counter, do another step, etc.) I believe that if you restructure your stored procedure to perform one operation on 1,000 rows as opposed to 1,000 operations on a single row, that you will get better performance. One thing that might help is to understand what a Tally table is. Use of a Tally table should be able to help you with your incrementing ID values. You should be able to find many blogs that tell you how to set up and use a Tally table. (Here is one example.)

Each single-record insert or update is a command that needs to be analyzed, executed and whose results need to be handled. By reducing the number of executions by a factor of roughly 1,000, you should see improved results. However we do not have your server, nor your data. So you'll need to do that footwork. From reading the two comments that you posted so far, I get the feeling that you're looking for one of us to wave a magic wand and have everything fixed, nice and easy. Performance tuning is rarely nice and easy.

Good luck.

  • I am just asking this question because it kills me last couple of days , the main problem it is not possible to reduce the execution number , because it is a checklist based application and each cheklist has so many checkpoints under that may be some checkpoints has sub checkpoints under that, and at the time of delegation i want to save all the details corresponding to checkpoins . there are lot of complicated business logic involved in it. I had read many blogs related to perfomence improvement but none of them helps me, that's why i had post it here Feb 3 '15 at 12:48
  • main problem is i can't find the number of checkpoints i want to insert into the database, all are created dynamically everything is dynamically created it may be 100 may be 1000 thats why i had used an iterating manner. loop through the numbers and save each one. i can't save all data with a single storedprocedure Feb 3 '15 at 12:54

I believe you can give a try with below 2 points as important aspect while moving ahead to improve the performance of stored procs.

  1. Use the SET NOCOUNT ON statement to prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure. For example, if you have eight operations in a stored procedure and you have not used this option eight messages are returned to the caller. Each message contains the number of affected rows for the respective statement.

  2. SQL Server always looks in the master database for a stored procedure that begins with the sp_ prefix. SQL Server then uses any supplied qualifiers such as the database name or owner. Therefore, if you use the sp_ prefix for a user-created stored procedure, and you put it in the current database, the master database is still checked first. This occurs even if you qualify the stored procedure with the database name. To avoid this issue, use a custom naming convention, and do not use the sp_ prefix.

  3. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.

Suggest you to go through below article which will help you in tuning you're stored procs:


Moreover its not necessary that tuning SP's will always increase the performance: Read below :


  • please go through the link provided in question these all methods are explain in that link but no use Feb 3 '15 at 11:36
  • But there are two links in my answer, go through them and figure out for any scope of performance on same!
    Feb 3 '15 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.