Query Plan Viewer

A Utility To Help You Optimize Queries

Perhaps the most complex aspect of SQL performance is query optimization. The database engine performs query optimization automatically, but the query structure itself can affect the overall performance and how the engine optimizes.

Nearly all queries can be written more than one way and yet return the same result set. For example, consider the simple query "SELECT * FROM table1." Assume that table1 has five columns named col1, col2, and so forth. You could write the query as "SELECT col1, col2, col3, col4, col5 FROM table1" to give the same result set.

Visually comparing these two queries, SELECT * appears much simpler than listing each column by name. However, listing each column by name in the query delivers a very slight performance boost. The reason is that with SELECT *, the asterisk symbol must be parsed into the column names. Such parsing is not needed when the query itself has already performed that task.

The best way to improve performance is to minimize the time required to run queries against the database. This appendix cannot discuss every possible query optimization because queries can be quite complex and can vary tremendously in structure. However, you can better determine how to optimize your queries by using Query Plan Viewer.

Query Plan Viewer is a graphical utility with which you can view query plans selected by the database engine. A query plan can be viewed for a SELECT, INSERT, UPDATE, or DELETE statement.