Database Reference
In-Depth Information
Figure 15-4.
Execution plans of a non-optimized query
You can optimize this query by adding another join predicate on the
OrderDate
column, as shown in Listing 15-8.
CHECK
constraints allow Query Optimizer to eliminate access to the tables, which cannot store the data for a particular
month. The execution plan is shown in Figure
15-5
.
Listing 15-8.
Selecting a list of customer orders with a specific item: Optimized version
select o.OrderId, o.OrderNum, o.OrderDate, i.Quantity, i.Price
from dbo.Orders o join dbo.OrderLineItems i on
o.OrderId = i.OrderId and
o.OrderDate = i.OrderDate
where
o.OrderDate >= '2014-01-01' and
o.OrderDate < '2014-02-01' and
o.CustomerId = @CustomerId and
i.ArticleId = @ArticleId
Figure 15-5.
Execution plans of an optimized query
