Database Reference
In-Depth Information
Chapter 19
Reduce Query Resource Use
In the previous chapter you focused on writing queries in such a way that they appropriately used indexes and
statistics. In this chapter, you'll make sure you're writing a queries in such a way that they don't use your resources in
inappropriate ways. There are ways to write queries that avoid using memory, CPU, and I/O, as well as ways to write
the queries that use more of these resources than you really should. I'll go over a number of mechanisms to ensure
your resources are used optimally by the queries under your control.
In this chapter, I cover the following topics:
•
Query designs that are less resource-intensive
•
Query designs that use the procedure cache effectively
•
Query designs that reduce network overhead
•
Techniques to reduce the transaction cost of a query
Avoiding Resource-Intensive Queries
Many database functionalities can be implemented using a variety of query techniques. The approach you should
take is to use query techniques that are resource friendly and set-based. These are a few techniques you can use to
reduce the footprint of a query:
•
Avoid data type conversion.
EXISTS
over
COUNT(*)
to verify data existence.
•
Use
UNION ALL
over
UNION
.
•
Use
•
Use indexes for aggregate and sort operations.
•
Avoid local variables in a batch query.
•
Be careful when naming stored procedures.
I cover these points in more detail in the next sections.
Avoid Data Type Conversion
SQL Server allows, in some instances (defined by the large table of data conversions available in Books Online),
a value/constant with different but compatible data types to be compared with a column's data. SQL Server
automatically converts the data from one data type to another. This process is called
implicit data type conversion.
Although useful, implicit conversion adds overhead to the query optimizer. To improve performance, use a
value/constant with the same data type as that of the column to which it is compared.
