As an independent consultant and trainer , I found myself doing a lot of existing code analysis and enhancement, mostly for stored procedures and ad-hoc SQL statements. I suspect a lot of people do the same thing as well, so I am really interested in learning how you do it. That’s the main purpose of this post. This post is tagged with Oracle, Sql Server, and MySQL, as the principals should be the same for all platforms.

Let me share with you how I do it. Notice that I look at table/column statistics and indexes in almost all the steps below. Therefore I purposely left them out in the discussion.

1. I always talk to the original author or the current owner of the code, asking him/her to walk me through it. I listen mostly, trying to understand why s/he writes code this way. I may ask a few questions, just to help me understand. I almost never do any lecturing at this stage. If the code is a stored procedure, I ask the author or stakeholder to give me sample execution statements, with parameters filled with typical values for production load. Sometime it is not possible to talk to the original author, because s/he may have left the company;

2. After that, I will get the database in question and restore it on my own workstation, with the data as close to production as possible. I will start running the code, and gather IO statistics and execution plan, and save the results for benchmark comparison later on;

3. Here is the raw code analysis stage, where code is put into an editor for analysis. In my case, it is VI or VIM. I find myself using the * command on all variable names and tables (make sure you are doing case insensitive search for Sql Server code), to see where they are used. This is also where I weed out the historical garbage code that is there but never being used anymore, such as unused variables, tables, etc. In the case of Sql Server, I mostly look for cursors, temp tables, and user defined functions;

4. Code simplification stage. I found a lot of bad performing code is unwieldy and unnecessarily complex. At this stage, I look for redundant WHERE clauses, convoluted AND and/or OR operators. This can be a tedious and time-consuming process. At this point, it is important to talk to the author or owner of the code, as a good understanding of data and entity relationship is crucial. Also, since I have gained some understanding from the steps above, it is easier to have an intelligent conversation with the code owner, with fresh perspective and momentum. Usually something good will come out of the conversation, and we will have something concrete to work on next;

5. I will make the changes discussed in step 4, once again, collect statistics and execution plan for comparison;

6. Repeat Step 4 and 5 a few times, when necessary. I usually start talking about better database design, code/design refactoring, set based operations, etc., as hopefully I have some credibility and gained the customer’s trust.

So what do you do to break down complex, low performance SQL code? What methodology, tools, tips, tricks you can share with me?