Optimizing a SQL Server stored procedure

Tags:
EXEC
SQL Server stored procedures
SQL Server tables
I want to optimize my stored procedure in SQL Server, as it takes some time to execute. It uses a temporary table to store some data, and then it uses a dynamic query that we execute using the EXEC statement. How can I remove temporary table or, in general, how can i improve performance of this stored procedure?

Answer Wiki

Thanks. We'll let you know when a new response is added.

There are many factors that could affect performance.

Without more information, it will be really hard to answer.

Can you post your code?

———

The first thing that you need to do is identify what statement within your stored procedure is causing you the most problems. You can do this by looking at the execution plan in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005+).

If you are doing index scans or table scans then you will probably need to add indexes to the table in question. This can include adding indexes to the temp table.

Instead of using a Temp Table you can try simply selecting directly from the temp table, unless there is a reason that you need the temp table. You can look into Common Table Expressions as a possible other option to a temp table.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following