Database Tuning

We often encounter situations where the client claims that “the database is slow”. Such slow performance is seldom caused by flaws in Oracle or SQL Server, but rather by improper use in an application.

The first item to check is indexes. Indexes provide the primary mechanism for tuning a database. An index provides the means for quickly finding records in a table as well as rapidly combining tables. Normally, every primary key, alternate key, and foreign key should be indexed. These indexes are critical — a missing index can degrade performance by orders of magnitude.

If indexes are intact and performance is still slow, the next item to check is other tuning structures. For example, the records in a table can be physically clustered based on a group of fields; this can enable rapid combination with other tables.

If performance is still slow, then check queries. SQL is a powerful language. In principle, a developer is supposed to state what data is desired and the database determines how to access it. The exact phrasing of a query is not supposed to matter as the database optimizer can restructure the query. In practice, the phrasing of a query can be important. For example, nested queries can slow performance so developers should try to avoid them.

These brief guidelines give you a flavor of database tuning. We are proficient at writing SQL code. We know how to deal with tricky situations and still get good performance.