SELECT * FROM Celko – December 2009

A few months ago, someone on an SQL Server newsgroup bragged that his old code is still running at some of his client’s sites. Knowing this guy, one reason is that his SQL code is such strong dialect, so blended with the application layer (i.e., he actually formats data for display in the database) and so proprietary that his clients (a.k.a. “victims”) cannot afford to leave him. We used to call this “job security programming” in the old days.

I have a known mania about writing the most portable code I can, using the most current common features. Standards über alle! What I get funny looks for doing is putting in code in comments with notes for the poor bastard that has to maintain the system after I am gone.

For example, I will put a commented out MERGE statement at the start of a long stored procedure which does the same job as that single statement. The newer, declarative, more relational features that are added to SQL are easy to write when you have the right mind-set. It is faster and more precise than writing a descriptive comment on the procedure! That makes it good documentation. And in a release or two, someone can remove the comment brackets, test the code and clean out the old stuff if it works.

But, the new features are not always faster than the old code. Think about it; the first release of a new feature is built on the old foundation, so it is often nothing more than “syntactic sugar” on the old engine. So, why do you do it, Joe?

Because a standard language element will eventually be optimized and a proprietary feature will probably be dropped. A professional programmer plans for the future on any platform, hardware or software.

E. B. Swanson was one of the first to categorize software maintenance (Pigoski,1996). He defined three different categories of software maintenance: corrective, adaptive, and perfective. The 1993 IEEE Standard on Software Maintenance further defined the categories and added a fourth preventive maintenance category.

Corrective Maintenance Corrective maintenance deals with fixing bugs in the code. You catch an SQL bug, and you are a hero. You waved down the train before it went off the collapsed bridge. This is classic maintenance work. In fact, it is the only definition most programmers know.

Almost all of us started with procedural languages and learned to find bugs by executing a program step by step. We had tools built for just that kind of task. I have found that most of the bugs in SQL are simple errors in specifications that lead to simple logic error. One programmer read a narrative differently than was intended.

Unfortunately, SQL bugs can be more complex. The DDL and DML have to work together so it is not enough to see a query without the table definitions. Some of the classic errors are:

1. A data element with different declarations in different table. The part number in Table A is not quite like the part number in Table B.
2. A data element in the wrong table.
3. The schema is not normalized.

Corrective maintenance accounts for approximately 20% of all software maintenance in traditional systems.

Adaptive MaintenanceAdaptive maintenance deals with adapting the software to new environments. That means new hardware or software. But it also means rules, contracts and laws. Many decades ago, I wrote a simple employee scheduling program in BASIC for a restaurant. One of the requirements was that the shifts had to have waiters who could serve beer. That meant they had a permit and were 18+ years of age. I had this hardwired as flag in the data. The legal age for alcohol consumption and the permits changed to 19 years of age in 1980, to 20 years of age in 1985 and then to 21 years of age in 1986. My little program scheduled a shift without anyone who could serve beer on the night the Alcoholic Beverage Control police decided to pull an inspection.

Programmers with a one-product view of databases forget that upgrading to a new release of their product is adaptive maintenance. Yes, it is probably easier than moving to a totally new database, but it is still adaptive maintenance.

The one-product shop is not how the world works today. An enterprise of any size will have several vendors or will work with an external database that is not their vendor’s product. This is also planning for failure. The first database was picked when the enterprise was small and new; that means a smaller OLTP system. It is probably not going to scale up if the company grows. Very few shops start off with multi-terabytes data warehouse.

One of the most neglected activities here is just keeping external data up to date. How often do you check to see that your ZIP codes are all current? USPS will send you updates. How are the other industry standards you use?

This is where standard portable code is first appreciated. I make distinctions among SQL that is (1) Standard code – clean, clear ANSI/ISO Standard SQL that runs on any platform; (2) Portable code – proprietary code that can be mechanically converted to ANSI/ISO Standard SQL (for example, the old getdate()in Sybase and SQL Server for CURRENT_TIMESTAMP in ANSI/ISO Standard SQL; (3) An equivalent proprietary code block that requires a more complex mapping to ANSI/ISO Standard SQL; (4) Something so weird you have no idea what to do (we are often back to job security programming).

Adaptive maintenance accounts for approximately 20% of software maintenance activities in traditional systems.

Perfective MaintenancePerfective maintenance deals with updating the software according to changes in user requirements. Basically the users are bitching. This is tricky. Are they just bitching? Are they right? There is usually a goal of improving maintainability, performance, or some other characteristic of the system.

In the database world, most of the time this means faster response time. That means keeping the schema the same but changing the physical storage – more or different indexes, partitioning and whatever else you can find.

End users do not see what we do to perfect the database. All they know is that “things seem faster” this week. Who knows about better disk usage or smaller storage footprints when they are looking a screen.

The application side of the system is really busy with this activity. The users see the interfaces and want the data reported in new ways. A change in how something is computed does not often change the raw data used to get the new answer.

Perfective maintenance comprises approximately 60% of all software maintenance in traditional systems.

Preventive MaintenanceIn 1993, the IEEE added a fourth category of software maintenance – preventive maintenance (Pigoski, 1996). This is not like corrective maintenance. Preventive maintenance is defined as maintenance performed for the purpose of preventing problems before they happen.

This category was created for safety-critical systems such as in aircraft and the space shuttle. By the time you would get a traditional bug report for corrective maintenance, people would be dead.

Vehvilainen (2000) defined preventive maintenance as software maintenance activities that are prepared and decided upon regularly. Like taking your car to the shop for scheduled maintenance, this is based on analysis of present conditions (funny red light on dashboard is blinking) and the forecasted needs of the software (get to the dealership for new parts and an oil change).

We can do some of this with monitoring tools that warn us about performance trends or let us model predictions about loads on the database. We all have been put on hold on an automatic telephone system that tells us that we can expect to reach a customer service representative in (n) seconds or minutes. Or days.

Chapin (2000) said the difference between scheduled and preventive maintenance was that preventive maintenance improves future maintainability. That is what I have been trying to do with my commented out Standard SQL code. Chapin also pointed out that most successful forecasting was done when software maintenance was performed on a scheduled basis (i.e., preventive maintenance should be incorporated into scheduled maintenance).
If you have been keeping count, you would notice that the total percentage for the other kinds of maintenance in traditional systems is already at 100%. This does not mean that preventive maintenance is free.

Unsupported Opinions I am going to stick my neck out, based on nothing but gut feelings, and say that the SQL/database side has different percentages in its maintainability. If you disagree, then say so. I want to get some discussion on this.

1. Corrective = 10%. If the schema was right at the start, the queries were easy to get right the first time. Most bugs are in applications, not the database.
2. Adaptive = 60%. This might be low. Updating from external data sources can be a real job. And you have to be current.
3. Perfective = 20%. You are always trying to squeeze a little something more out of the hardware and software
4. Preventive = 10%. I think it should be higher, but I do not believe it is.