I inherited a big horrible stored procedure (sp) created by unioning two other sp’s together to make a big mess.

This sp was feeding a report, and although it was returning the correct data, was taking a significant time to do so.

These are the steps I took to tame it …

1. Change the sp code into a query
2. Save a copy of the original, because you never know 🙂
3. Save the code you are working on (regularly).
3. Run it. Save the output and record the number of rows returned.
4. Expand all views, and views of views, etc
4b. run it after each change – be pedantic – then save.
5. Remove all commented-out lines of code
6. Comment-out any select items (in the outer select) not needed by the report.
7. Working down from the top, comment-out any Select items in sub queries not used by the parent.
8. Save all sub-queries into temp-tables.
9. Tune the slowest sub-query

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s