Many relational databases have some kind of automated counter or auto counter. An auto counter is a specific database object used to count integer values, usually from 1 upwards. Auto counters are often used by a database to fill primary key surrogate key integer values. In the old and dirty days, before the invention of auto counters, items such as unique surrogate keys were either not used or stored in a single central system metadata table. A single central system data table is potentially disastrous in highly concurrent environments, resulting in a serious hot block problem on that system data table. A hot block problem is where enormous quantities of queries access the same physical area of the disk, all at the same time (concurrently). Multiple operations compete for the same resources. Eventually resources become scarce and performance suffers.
This section expanded on details provided in Chapter 5 by going into detail of performance tuning of SQL code, queries in particular. Poorly designed table structures ultimately equate to queries, which are quite likely impossible to tune up to an acceptable level of performance. Well-designed table structures propagate into high-performance queries. Indexing is also important to query performance, and overall database model performance.
The above tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.
This was first published in April 2006