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
Best Answer
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.