Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook
A step by step guide to designing and tuning your SQL Server DBMS for maximum performance.
TABLE OF CONTENTS Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook
4.3: Query optimization
4.3 Query optimization
The query optimization phase is the phase we will concern ourselves with in this chapter. This phase can be broken down into a number of logical steps, as follows:
-
Query analysis
-
Index selection
-
Join order selection
Let us discuss each step in sequence.
4.3.1 Query analysis
The first step the query optimizer performs during the query optimization phase is query analysis. In this step the query optimizer examines the query for search arguments (SARGs), the use of the OR operator, and join conditions.
Search arguments
A search argument is the part of a query that restricts the result set. Hopefully, if indexes have been chosen carefully, an index can be used to support the search argument. Examples of search arguments are as follows:
account_no = 7665332 balance > 30 lname = 'Burrows'
The AND operator can be used to connect conditions, so another example of a valid search argument would be as follows:
balance > 30 AND lname = 'Burrows'
Examples of common operators that are valid in a search argument are =, >, <, ?, and ?. Other operators such as BETWEEN and LIKE are also valid, because the query optimizer can represent them with the common operators listed above. For example, a BETWEEN can always be represented as ? AND ?. For example:
balance BETWEEN 1000 AND 10000
becomes:
balance >= 1000 AND balance <= 10000
A LIKE can...
Copyright Butterworth-Heinemann 2001 under license agreement with Books24x7
