Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2010-11-13
5
Medium Priority
?
399 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 1500 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

580 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