An older book review on Ted Neward’s blog reminded me to hold forth on the subject of database tuning. Any application that has a relational database at its heart requires performance tuning. The development team needs to either hire or cultivate the skills required to extract the most out of the database, skills that go far beyond the basic select/insert/update/delete statement that most OO programmers know. This skillset is just as important as the analysis and “regular” programming skills that are obvious requirements for the team. For some reason we software folk tend to minimize the importance of database knowledge, at our obvious peril.

Case in point: recently, my team received complaints about the performance of a message-handling interface. This particular interface accepts messages that result in the creation or update of records in a relational database. In the process of figuring out whether to insert new records or update existing ones, the message handler called a stored procedure that accepts five parameters. This stored procedure is also called from other parts of the application. Being good software engineers, we had one stored procedure to do lookups on a particular table. Since the lookups can be done on any of five elements, but no single lookup ever passes a value for all five, the stored procedure had five COALESCE statements in the WHERE clause of its SELECT statement. The parameters that aren’t passed are essentially excluded from the query by the COALESCE statements.
This is a stellar example of centralization and standardization, but it turned out to be a donkey from a performance perspective. The message handler always called the lookup stored procedure with values for the same two parameters and NULL for the other three. Adding an index for the two columns that the message handler used didn’t provide much of a benefit. We hit pay dirt when we rewrote the stored procedure to test the parameters for nullity and call a specialized stored procedure that only compared against the two columns we knew the message handler used. Processing time went from about one message every two seconds to five messages per second – that’s an order-of-magnitude improvement.
The lesson? No matter how nuanced your programming of your application container, if your database programming is not up to par, your application won’t perform. In the absence of a skilled DBA, we programmers need to know how to use all of the available tools (such as execution plans and traces) to find performance problems and eliminate them.