iltering is an important aspect of writing most complex queries. While there may be instances where you don't need a filter, such as a simple query that retrieves a list of all U.S. states, in the majority of cases you need to filter the data to a more precise set. For example, you might need to obtain a list of all U.S. states that begin with the letter "I."
Developers and DBAs have many operators available to perform such filters. Query performance is definitely a very high consideration when deciding which operator to use. There are some experts who claim that certain operators generally perform better than others, and provide rules of thumb to guide your choices. While these claims may
be generally true, there are a number of influences on query performance in any individual case that can prove more important than the general rule. Basing the decision to select an operator strictly on a rule of thumb over the study of individual query usage and execution plans could result in a query that has less than optimal performance.
Choosing the Optimal Operator
When approaching the selection of an optimal operator, first consider how the query will be used. If the query is to be used infrequently and is part of a process that executes when there is low activity, such as an overnight SQL Server Integration Service or Data Transformation Service package, a long-running query may be acceptable. Alternatively, if the available time is short, such as a major online shopping outlet that may need to execute a query for each transaction, you'd want to spend the time to get optimal query performance—both to preserve the customer's experience and to preserve database server stability.
You'll also want to consider how and where any filtering values are obtained. Is the filtering value a single value or multiple values? Some operators accommodate multiple values better than others. Obtaining multiple values from another table or sub-query rather than passing them specifically may also affect which operator you should select.
It's also important to consider indexes when approaching operator selection. When an appropriate index is available, the SQL Query Optimizer will usually use that index to perform an index seek. If the index does not exist or is not optimal, a the database must perform a table scan or index scan—typically resulting in reduced performance. However, if the table that is being queried contains relatively few rows, a table scan or index scan may actually be faster than an index seek. You'll want to take such conditions into account when evaluating execution plans.
Evaluating Execution Plans
Blanket statements such as "the EXISTS logical operator performs better than the IN logical operator," make the cynic in me clear his throat and tap me on the shoulder. The decision to select one operator over another cannot be determined solely by taking someone's word for it. Evaluating execution plans to test your queries for the database in which the query is being written is a better approach. Execution plans reveal whether table scans or index seeks are being performed, and illustrate the relative performance of the various parts of the query.
You can view execution plans using the Query Analyzer tool in Enterprise Manager (for SQL Server 2000) or through SQL Server Management Studio (for SQL Server 2005).