Databases are a lot like people. As they get older, they get fatter, slower, and less efficient. Their digital arteries get clogged with the accumulated results of years of operation and they can finally get to the point where they either stop working or work so slowly that their usefulness is compromised. As a courtesy to the reader and as a concession to my own circumferentially-challenged physique, I promise not to take this analogy any further!
Many issues arise because of initial design decisions that do not take into account future growth plans for the database. In the rush that often accompanies bringing a product to market, emphasis is often placed on getting the product out the door rather than taking the time to analyze and project what the database will look like one or two years after its installation.
Often software vendors consider these issues to belong to the customer rather than to the vendor. The problem is that by the time the customer is familiar enough with the product and aware of the magnitude of the data, it is a major undertaking to go back and fix the early setup and database layout problems. It is possible to make these types of changes to an existing system that is full of data, but it usually entails rebuilding very large tables. It makes a lot more sense for the designer to plan for the system's inevitable growth and to make the proper decisions at design time or at install time.
Some of the worst database design failures come when a vendor is trying to design an application that runs on multiple databases. Developers tend to view database systems as commodity "black boxes" that are totally interchangeable. Not knowing the database on which the system will be running means that they tend to view the database at the conceptual rather than at the physical level. They usually have no idea how or where the data is to be stored, and their view ends at the table level. Anything else is usually considered to be the responsibility of the onsite database administrator (DBA).
This is totally wrong. You can see the results of this thinking in systems that choke after six months or one year of use. Any system can run well with very low data volumes. Only a system that has been designed with the physical data layout in mind can make the transition from small database to large database successfully.
Database designers usually do a fairly good job in the logical design of databases. Such areas as normalization and an indexing strategy tend to be fairly well understood. Where designers typically fall down is in the area of physical database design, especially in the areas of extent sizing and table design. This 10-Minute Solution shows you how to design your system properly so that these issues are addressed.
Your database is slowing down with age, probably as a result of poor physical design. How do you make your database handle larger volumes of data more efficiently?
Make these decisions about the physical design of your database system:
- Estimate the future database's size and growth rate.
- Size the computer and disk systems properly so they can handle the data volume.
- Design your tables with the physical layout and structure in mind.
- Consider data archiving, purging, and cleaning as you design the table structure.
