Chapter 37. Strategies for unraveling tangled code

 

Jennifer McCown

When you inherit an application or a database, you often become the proud owner of overly complex, poorly performing T-SQL code. These queries rarely come equipped with meaningful documentation or with system architects who are willing and able to explain the code.

This chapter covers several methods to make sense of the mess. The overall approach is to organize, break down, and then streamline the code to identify and remove some of the nastier coding mistakes and inefficiencies.

The sample database is a small model of a vending machine business, called VendCo. Tables in the VendUnit schema store data about each vending machine (including model and location information) and product inventory. Maintenance and Product schema hold technician and sales product data, respectively. You can download the scripts to create and populate the database from my blog: http://www.MidnightDBA.com/Jen/Articles/.

Organize: make it readable

Imagine that you’ve just received (or discovered) an undocumented mystery query. The first thing you notice is that the query is a solid chunk of code, without spacing or line breaks. Before you can do anything with the query, get it into a readable format and begin adding comments. The following two sections show you how.

Formatting

Break down: what does it do?

Streamline: resolve obvious issues

Streamline: optimize joins

Streamline: similar subqueries and queries

Streamline: dynamic SQL

Summary

About the author