asked on

SP is taking time while execution. Need to fine tune for faster execution

I am trying to execute an SP attached with this question usp-importbossdevice.sql.
The sp picks data from one table and Insert/Update/Delete data in destination table based on some rules/criteria/condition. The sp also exports data in destination table to a log table for archive purpose. There are calls to another sp for generating device id, user id and spare id.
There are 5000 rows is source table and 8000 rows in destination table.
When i execute the sp it is taking lot of time. I stopped the sp after 15 minutes. I am not able to see the exact problem

celazkon

Hi, create indexes in the tables before running the procedure.

Anthony Perkins

To be honest, it is not surprising it is taking so long. The Stored Procedure executes in a loop which is the worst way to execute any SQL Query. Perhaps that is the only practical way you can do it, if that is the case than you are simply SOL.

So to summarize, if you are not prepared to re-write the whole Stored Procedure using good practices with set operations instead of looping for every row, than no amount of tweaking is going to yield any big differences.

Good luck.

ASKER CERTIFIED SOLUTION

subhashpunia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dba2dba

You can use DTA to get some index recommendations for the procedure.

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-database-tuning-advisor.html

maheshpappu

ASKER

the tips provided will partially help in writing a proper sp. But this is not the solution to the problem mentioned above.