Stored Procedure Tuning [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the 开发者_如何学JAVAhelp center. Closed 11 years ago.

I have a big stored procedure, which runs for 2-3 secs at 100% CPU. Its not a big deal, if the SP runs once for a while, however this particular SP runs every second. so you can understand the total processor usage in a minute and through-out the day.

This stored procedure contains lots of queries, temporary tables, one or two cursors and some dynamic queries.

what will the best possible solution to tune-up my SP, and different ways to do that.

What are the points we need to remember while writing an SP in general?

Please Help...

---

To tune up your stored procedure you need to address each query and process contained within it individually. Make sure that your indexes are set correctly and check whether your approach with the temp tables and dynamic SQL is appropriate. Also make sure that you're not abusing other stored procs and views that may have been designed to be used directly rather than as cogs in a larger process.

It is difficult to make more suggestions but, from your description on what is entailed in the stored procedure and how often it runs, it sounds like this is a key process in your system and the data involved a likely candidate for denormalization. If you denormalize the data then you would rewrite the stored proc to query that part of the database to get better performance.