The world of performance tuning, specifically query tuning, is quite vast, with entire books devoted to the subject. And those books come with pages and pages of dense and complex technical material. But in your quest to figure out how to get more performance from that slower-than-molasses stored procedure, don’t forget some basic steps:
- Define Success. Before you do anything, decide what the goal is? A 50% reduction in time needed. 75%? What?? And once you have met the goal stated for success, stop! I’m sure you have other tasks on your to-do list. Nobody in your company is going to care if you make it faster then needed. When the customer/end user is happy, move on.
- Do no harm. Whatever you do, don’t make the problem worst then it is. For example don’t throw a bunch of indexes into your database in an attempt to fix one procedure, without understanding the rest of the queries in your system. It is better to start off with a review of the code first. Changing the code will only affect that procedure where as creating or dropping indexes will surely affect other queries on your system. And please fully test all changes you do make! The last thing you want to do is introduce logical errors into your code.
- Document your changes. Oh yes, the dreaded need for documentation. I know all of the excuses for not documenting a code change…you’re too busy…nobody will read it…nobody will care. Trust me, the new developer who comes in behind you will be so grateful if you do this. And if you need to work with the same code again, you’ll be happy if you document your changes, because you will probably forget what you did 6 months later! So, write down what you changed before you forget!
