Resource-intensive Queries

Some types of queries are inherently resource-intensive. This is related to fundamental database and index issues common to most relational databases, including SQL Server. They are not inefficient, as the optimizer will execute the queries in the most efficient fashion possible. However, they are resource-intensive, and the set-oriented nature of SQL Server may make them appear inefficient. No degree of optimizer intelligence can eliminate the inherent resource cost of these constructs. They are intrinsically costly when compared to a more simple query. Although SQL Server will use the most optimum access plan, this is limited by what is fundamentally possible.

For example, the following query characteristics are resource-intensive:

Large results sets
IN, NOT IN, and OR queries
<> (not equal)
Row aggregate functions, such as SUM
Local variables, expressions, or data conversions in the WHERE clause
Highly nonunique WHERE clause
Complex views with GROUP BY or ORDER BY

Various factors may require the use of some of these query constructs. The effect of these will be lessened if the optimizer can restrict the results set before applying the resource-intensive portion of the query. For example, the following restricted query is efficient:

SELECT SUM(qty) FROM sales
WHERE stor_id = 7131

If an index exists on the department column, the optimizer will likely use it to restrict the results set before applying the SUM operation, which can improve performance. Compare that with the following unrestricted and inefficient query, in which the sum operation must read every row in the table:

SELECT SUM(qty) FROM sales

For example, the following query is efficient:

SELECT * FROM phonebook
WHERE last_name = @var
AND zip_code = 98052

If an index exists on the zip_code column, the optimizer will likely use it to restrict the results set, which can improve performance. Compare that with the following inefficient query:

SELECT * FROM phonebook
WHERE last_name = @var

The value in the local variable is only known at run time. Because the optimizer builds the access plan and makes index usage decisions at compile time, the optimizer cannot use the value in the local variable to restrict the results set, even with an index on the zip_code column.