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
maheshpappuAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
subhashpuniaConnect With a Mentor Commented:
0
 
celazkonCommented:
Hi, create indexes in the tables before running the procedure.
0
 
Anthony PerkinsCommented:
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.
0
 
dba2dbaCommented:
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

0
 
maheshpappuAuthor Commented:
the tips provided will partially help in writing a proper sp. But  this is not the solution to the problem mentioned above.
0
All Courses

From novice to tech pro — start learning today.