Solved

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

Posted on 2010-11-13
5
359 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:maheshpappu
5 Comments
 
LVL 7

Expert Comment

by:celazkon
ID: 34127515
Hi, create indexes in the tables before running the procedure.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34128796
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
 
LVL 6

Accepted Solution

by:
subhashpunia earned 500 total points
ID: 34134981
0
 
LVL 8

Expert Comment

by:dba2dba
ID: 34138478
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
 

Author Closing Comment

by:maheshpappu
ID: 34194421
the tips provided will partially help in writing a proper sp. But  this is not the solution to the problem mentioned above.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question