Tips for optimizing sql commands worrying about legacy

Question

The concern with the legacy of the SQL statements is a constant in my head. Especially when SCRUM is used, where the code has no owner, that is, all must be able to repair and maintain each piece. Optimizing SQL procedures usually means converting it into a set-based commands and using special operators. I need tips to keep the code working without forgetting the threshold optimization x readability.

Answer 1

Comments. If it's a newer command or an obscure one, make sure to leave a comment associated with the statement describing it in code/source. That way you don't have another developer down the road refactoring the statement to improve readability at the cost of performance. My general guideline with this is if someone of intermediate skill level or below would have to spend several minutes or more searching for what the statement is really doing, leave the comment to save them time.

Answer 2

I wouldn't worry so much about readability other than having the formatting conform to defined standards. Optimization is much more important than using only simple SQL that anyone can understand. That is where comments should come in... Explain what the SQL should be doing and why you chose a certain optimization technique. The added advantage to this is that it will help the next person who reads it to learn new SQL techniques.

Answer 3

I've found the best solution to be to include, in your comments, a clearly qualified, duplicable optimization test for the query, using statistics from the optimizer. (This also works nicely for stored procedures, where the same issues appear.)

Include a statement about the nature of the testing context (hardware and data), data generation code if necessary, and a clear description of testing conditions (cache settings, repetitions, etc.) Better yet, agree on a team template for this spec.

Then enforcing comparisons needs to be built into your culture somewhere ... the best solution would be for the culture to expect documented before-and-after optimization testing.