What is the Correct Solution to Database Performance?

I was at lunch with some colleagues the other day and the topic of database performance was brought up. This topic never goes away. The expectations of the end-users is a moving target that never seems realistic. Or is it? As we ate our lunch we all threw out ideas on the symptoms and solutions. No magic bullet but it came down to really two different approaches for a possible solutions depending on where management see’s the value of the proposed solution.

Let’s look at a common type of poorly performing database. Typically some sort of home grown application or third party app that has been grossly modified over several years that utilizes a database. In the beginning it was great. Everyone was happy. Then someone asked the question: “can we get it to do this?” and someone in the room said so-n-so can do it from the app/dev team. You get the idea. Changes kept occurring over the years. The use and expansion of the application and the database is snowballing. Then one day someone notices this is not as quick as it used to be. Now all the players are in a meeting wondering what can be done to get this back to its original performance level. Here comes the finger pointing. One group blames a poorly written query for an application, another says the data needs to be restructured and another thinks it is hardware. IT IS ALL OF THEM! Where do we start?

The first approach is to take a deep dive into the data. Often times the database has redundant data across many tables within the database. For example let’s look at typical user information: First name, Last Name, address, city, state, zip code, home phone, cell phone, birthday, and social security number. Does each table need to have this duplicate information in it? The answer is no but when the new “feature” was asked for the developer did what they could within the time frame that management gave them. So they created their own NEW table for the feature to use. More and more features get added by a variety of developers and it continues for years. Similar data in multiple tables or databases but not in the same structure. To correct the database structure is very labor intensive. Not many managers want to make this type of personnel resource commitment. However pursuing this type of solution often leads to better efficiencies within the database that have long lasting effects for both support staff and the end users.

The second approach makes us evaluate the hardware we are using. Let’s face it, hardware gets blamed a lot. Does it have enough RAM for the system? Is there a disk issue? How about the network? Is it the SAN? I have been asked many times to look at the performance of the hardware and for the most part it is operating within the specs. RAM is relatively cheap to upgrade and management typically doesn’t have a problem spending money on this type of hardware. So is upgrading from a mechanical spinning disk to flash disk or solid state disks. Increasing network bandwidth is not cheap nor is upgrading the SAN but often this is NOT where the performance issues are. Don't get me wrong, there is nothing like utilizing an ALL FLASH disk array for databases. Throwing hardware at issues is the quick way to gain back performance, it just requires management to be willing to pay for it.

What is the right solution? New hardware will only mask the underlying issues of a poorly performing database. Committing resources to restructure data can take a great deal of time. In my opinion, the solution has to be blended. Restructuring of the data will have the biggest ROI followed up with a few hardware upgrades that make sense. Both are a hard sell to management but remember the goal is to make everything faster for the end-user. And we all know that when the end-users are happy so is management.