Database Reference
In-Depth Information
Similarly, creating an index on the columns referred to in an
ORDER BY
clause helps the optimizer organize the
result set fast because the column values are prearranged in the index. The internal implementation of the
GROUP BY
clause also sorts the column values first because sorted column values allow the adjacent matching values to be
grouped quickly. Therefore, like the
ORDER BY
clause, the
GROUP BY
clause also benefits from having the values of the
columns referred to in the
GROUP BY
clause sorted in advance.
Avoid Local Variables in a Batch Query
Often, multiple queries are submitted together as a batch, avoiding multiple network round-trips. It's common to use
local variables in a query batch to pass a value between the individual queries. However, using local variables in the
WHERE
clause of a query in a batch doesn't allow the optimizer to generate an efficient execution plan.
To understand how the use of a local variable in the
WHERE
clause of a query in a batch can affect performance,
consider the following batch query (
--batch
):
DECLARE @Id INT = 1;
SELECT pod.LineTotal,
poh.OrderDate
FROM Purchasing.PurchaseOrderDetail AS pod
JOIN Purchasing.PurchaseOrderHeader AS poh
ON poh.PurchaseOrderID = pod.PurchaseOrderID
WHERE poh.PurchaseOrderID >= @Id;
Figure
19-7
shows the execution plan of this
SELECT
statement.
Figure 19-7.
Execution plan showing the effect of a local variable in a batch query
As you can see, a
Clustered Index Seek
operation is performed to access the rows from the
Purchasing.PurchaseOrderDetail
table. If the
SELECT
statement is executed without using the local variable, by
replacing the local variable value with an appropriate constant value as in the following query, the optimizer
makes different choices.
SELECT pod.LineTotal,
poh.OrderDate
FROM Purchasing.PurchaseOrderDetail AS pod
JOIN Purchasing.PurchaseOrderHeader AS poh
ON poh.PurchaseOrderID = pod.PurchaseOrderID
WHERE poh.PurchaseOrderID >=1;
