Related Links

Web Seminars

If you've made it alive through the technology downturn over the past few years, chances are you've had to do more for less. One of the things I've found myself doing more often than before is performance tuning my physical data models instead of handing them off to a DBA team for physical design. All of the "fox in the hen house" analogies apply, but coming from the logical modeling side of the house, I tend to be more conservative in making tuning changes due to the inevitable loss of data integrity. Nevertheless, most shops today are still willing to trade off data integrity for performance gains. As a result, I've compiled a list of some data model performance-tuning candidates for consideration when you start your physical data model tuning process. If you still hand off your logical model to another team for physical design, hopefully this article will help you (and your DBA teammate) make intelligent decisions.

It makes sense that this article is primarily applicable for online transaction processing (OLTP) data models and not online analytical processing (OLAP) data models. This is primarily due to two facts: all warehouses are not responsible for upholding the enterprise business rules, and dimensional models are designed for easy-read access, not normalized data.

The other ground rule in this article is that the logical data model always reflects the business rules. Therefore, when we discuss tuning we are really discussing denormalization or derivations of the physical data model.

In this article, I'll discuss super- and subtype performance considerations.

Ease of query you say? Certainly some queries on normalized models can be complex. However, making reads easier usually makes inserts, updates and deletes harder. Furthermore, if you design your code for reuse, you only need to write the SQL once.

For the sake of brevity of this article, the rules of normalization will not be covered. If you'd like to see some good articles on normalization, I suggest reading Tom Johnston's many articles dealing with the topic.

A Few Prerequisites to Performance Tuning

Before we start tuning the physical data model, there are a few things you should consider.

First, understand that the data model may not be the source of your performance problems. Performance problems can be difficult to locate and identify. Some potential performance bottlenecks are:

• The application - The source code may be written inefficiently, causing bad performance.
• The network - Many of today's applications utilize overworked local area networks (LANs), wide area networks (WANs) or, even worse, the Internet with its many inherent problems.
• The database - The complexity of some of today's leading relational database products is astounding. While brushing up on some Oracle tuning procedures, I referenced Oracles 9i Performance Tuning manual, weighing in at a whopping 810 pages.

Before you start deriving and denormalizing the data model, make sure the other parts of the puzzle are put together properly.

Noted modeling expert Graeme Simsion recently stated in a column,"It's much easier to build performance into the design than to try to build it on."

This brings me to my second point: understand your performance requirements. Performance requirements are just as important and valid as other business requirements. The earlier you identify the performance requirements, the earlier you can make design decisions to improve your performance. If you don't have a documented performance requirement, you can't measure your success at the end of the tuning process.

Third, make sure you have a defined testing methodology. Your testing methodology should be repeatable, defined, managed and finally optimized. Sound familiar? It should; it's straight from the capability maturity model.

Don't Make Your Problem Someone Else's

If I remove a business rule from my database and put the responsibility for that business rule in the application, have I improved my performance?

If you have narrow-minded scope, you may be inclined to answer yes to this question. However, if your scope includes the application, as it should, then the answer may be no. Simply moving business rule responsibility from one application group to another may seem like a good performance tune, but you may be making your problem someone else's.

Even worse, you may be removing business rules that belong in the database and thereby inversely impacting your data integrity.

As the data architect, always keep in mind that you must have enterprise scope when designing your databases. It is not reasonable to expect the data requirements for an application won't be needed by many other applications throughout the enterprise. For this important reason, I strive to keep my data structures application independent.

Performance Tune #1 - Subtypes

Subtypes are the perfect way to show attribute optionality or relationship rules that vary by entity occurrence. That is, if I have an attribute or relationship that is mandatory for a subset of entity occurrences, I can reflect those business rules using a super/subtype data structure. Take the Employee model fragment in Figure 1, for example. For all Employees, I store the Employee name and address. I also store the Employee Type Code, known as a classifying attribute or discriminator, which tells me the Employee type (Full-Time or Temporary). For Full-Time Employees, I store the Hire Date, Job Grade, Salary Amount and Social Security number. For Temporary Employees, I store the Contract Identifier, Hourly Pay Rate and Tax Identifier.