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