Lisa Bohm - Refactoring Legacy T-SQL for Improved Performance: Modern Practices for SQL Server Applications

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the books product page, located at www.apress.com/9781484255803 . For more detailed information, please visit http://www.apress.com/source-code .

What is legacy code? There are a few definitions floating around out there, but as a working definition, were going to use the following:

Legacy code is code that is no longer being actively supported by the people who wrote it.

Why are we going to use that? In software development, good documentation goes a long way. Developers should understand what code is trying to accomplish and how its trying to do so. When documentation either doesnt exist or isnt as thorough as required and the original programmers arent available if you need to know why something was written a particular way, it can be a nightmare to fix. In some cases, it may not even be clear whether code ever worked as intended, or if the functionality of the change someone is requesting is within the original intent of the programmer(s).

How does legacy code start? Lets look at this story. Code is written to solve a problem for example, someone is copying data into Excel every day and doing some hand manipulation to generate a graph to add to a larger report. A developer sets up a quick application to pull the data from the database and export it into Excel automatically for the user, also performing the calculations the user was doing by hand.

This user then trains their successor and another person in the department on how to view this report. One of them says, Hey, this is great! Can you also make it pull data for this other report and we can show how these numbers reflect against each other? Someone else loves the additional functionality but needs the code to work in a different way, or do different statistical calculations, or needs to add an additional field on the report. That persons manager is intrigued by the functionality and wants a weekly summary report to review. Code structure starts to resemble something that is cobbled together, as multiple developers add bits of functionality over time. Oftentimes, there is little to no documentation on the functionality or the choice of code everyone just adds a bunch of lines at the end of the code to handle the small part they were asked to develop.

Many times, front-end developers dont specialize in T-SQL, so do not usually have a deep understanding of the SQL Server optimizer. Especially in the case of lets just add lines of code to the bottom of this to handle additional functionality, calls to the database can increase exponentially; in many cases, calls grab the same data over and over. And, oh, by now, over half the company is using this app in one way or another or perhaps three ways. The vast majority of these uses, by the way, were never intended by anyone who had ever touched the code.

Users complain about slowness and performance. Even more frustrating, all of the other business-critical applications that use the same database(s) become slower and slower as they fight for resources and locks with the application and its chatty data calls. Also, of course, every developer that has ever touched this application has moved on or has been promoted and hasnt looked at code for years, so has no recollection of ever manipulating any code even remotely similar to this patched-together behemoth that is rampaging through the company infrastructure.

You have inherited one of these types of applications, or you probably wouldnt be here reading this book. Although there will be (possibly many) times that you may want to cry, yell, or swear, this will also give you some unparalleled opportunities to be a hero and pull off some very spectacular-seeming fixes. Just remember, though, that when you really fix something amazing, most people will be completely oblivious to that fact. Then, when you do something you think is so obvious that a worm out there on the sidewalk could probably manage it, you may get so many congratulations and thanks that youll wonder if you really did something magical. That is probably more of a general life/job observation and not related specifically to legacy code, but its also prevalent here.

We are going to continue on from the point of Okay, this has been identified as an issue. Now what do I do with it? Most of what well be doing is actually looking at the code with the help of a few performance measures and learning about best practices to help identify problem areas. You should be familiar with basic T-SQL coding syntax and techniques and how to do a bit more advanced querying.