"Frank's skill in asking the right questions is un-mistakable, and is at the core of his leadership philosophy.

The power of these questions cannot be underestimated, especially if you want to lead and not manage."
—John Cave
Westhaven Worldwide Logistics
After a recent database review session our development dba pointed out to me that he wants every table's primary key to be a identity column. I did not like this suggestion because I believe when dealing with reference tables the primary key should the same as the logical key. For transactional tables it makes more sense to the link the primary key to a physical key. Well after some research his logic starts to make sense. Here is why.
Hannes Strydom

How to speed up a database which has gotten slow

Preface

While the actual database design usually is able to handle the amount of stored data without major performance problems newly added clients and their data volume changes that.  Once the size of a database grows close to 1 GB, the number of concurrent user exceeds 20 or the number of new a-records goes beyond 200 an hour, the need for speed improvement is raised.

This document is not in search for reasons why a database is designed the way it is.

In general there are two types of improvements involved: Direct and indirect.

It has to be understood that direct changes always force changes in the way client applications access the data.  This document only considers direct changes.

Note:
The best way to get performance is to pay up-front for tuning.  It is not a hidden cost if included in the initial design effort and is exponentially cheaper than performing tuning as an add-on feature.

Put simply, tuning involves three basic activities:

  1. Eliminating unnecessary processing.
  2. Eliminating redundant processing.
  3. Using more efficient processing in exchange for less efficient processing.

Usually you start of with the existing diagram of your database.

Oops - you do not have one?  What about reverse engineering?  Most case tools offer that option.

Unfortunately too often reversed-engineered diagrams of existing databases are an unreadable mess of 60 pages or more.  This easily proves that databases grow into far too complex systems that do not allow the easy use of a case tool anymore.  It is recommended to take the proper steps to not only simplify the database but to also force the existence of an easily readable and up-to-date diagram.

"Performance Design Review" should be part of both the "Database Design Review" and "Application Design Review" processes.

These are experiences while working with many different databases sized 2 GB up to 100 GB.  Some of the information in this document can be found on the Internet as others faced the same challenges.

To simplify things blob fields and large varchar field (> 254 characters, but mainly > 2048 characters) will be named blobs and seen as synonym.

There are two major times when the behavior of databases changes:  Once the database hits the 2GB size and then once again when the DB grows over 10GB.  Once a table hits more then 10 million rows there will be changes in the behavior too, but they are less compared to the 2 GB size.  And usually they appear around the same time, especially in non normalized databases.  Some of the speed problems appear earlier, but they are usually covered by the use of expensive hardware or spending lots of time to fine tune the used hardware and/or software.

Once a database exceeds the 2GB there will be a few others - name them natural - issues:

Redundancy of data

Some of the described speed improvements will lead to more redundancy of data.  While this is an unwanted side effect the improved speed makes up for it.

Possible Optimizations

There are quite a few major areas of improvement, which can gain more speed with less work:


 
↑ Top

PDF version

This Genius One Article is published here for the purpose of helping to improve your personal and organizational performances; designed to provide accurate and authoritative information in regard to the subject matter covered. If legal advice or other expert assistance is required, the services of a competent professional should be sought.

Please contact us directly if you need a word or pdf version of this article.