SQL Performance Tuning

We said in Chapter 1, "Facilis Descensus Averni," that we assume you're already familiar with (among other things) indexes, but here's a quick summary of index basics to refresh your memory.

Because the SQL Standard doesn't deal with physical storage of database objects or data, no official "standard" syntax exists for creating an index. However, because the foundation of a good DBMS's performance is its indexes, all DBMSs support the creation of indexes in some way, usually via the nonstandard SQL-extension CREATE INDEX statement. The de facto standard syntax, supported by each of the Big Eight is:

CREATE [ UNIQUE ] INDEX <Index name> ON <Table> ( <column> [, ...] )

CREATE INDEX makes an index on one or more columns of a table. By default, the values are indexed in ascending (ASC) order, but you can index them in descending order by adding the keyword DESC somewhere, usually after the column name. If you want to prohibit duplicate values in the indexed column(s), add the keyword UNIQUE to your CREATE INDEX statement.

Strictly speaking, it's the DBA who sets the rules for creating and dropping indexes. Nevertheless, common practice is to give programmers the right to say if an index is necessary. And certainly the database's users have the right to employ indexes. In fact, they would have a hard time avoiding them. Typically a DBMS will read an index to handle these situations:

• An expression of the form <column> <predicate> <literal> in a WHERE clause.

• An expression of the form <column> = <column> in an ON clause.

• DISTINCT, UNION, and ORDER BY.

• Sometimes, to evaluate a column expression in the select list.

• To avoid sorts. Sorts are generally fairly expensive, and the time needed to do a sort grows exponentially with the size of a table.

• To maintain UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints.

An index helps the DBMS solve a query faster by allowing it to avoid a sequential search, or full table scan. It's a well-known fact that this SQL statement:

SELECT * FROM Table1 WHERE column1 = 5

will be evaluated much faster if column1 has a distributed set of indexed valuesand provided that Table1 is fairly large. So indexing speeds up SELECT. The price you pay for this is that INSERT, UPDATE, and DELETE will be slower on the indexed columns. Thus, if your code contains many SELECT statements with the same column in the WHERE clause, you should ensure that column is indexed. And if your code contains massive data-change statements involving the same column, it's a good idea to make sure it's (at least temporarily) not indexed. (Note that we're not talking about indexes used by the DBMS to enforce constraints herewe just mean explicitly created indexes that don't involve the access path needed to make the data change.)