Database Workload Optimization - SQL Server Query Performance Tuning - page 524

FROM Purchasing.PurchaseOrderHeader AS poh
JOIN Purchasing.PurchaseOrderDetail AS pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
JOIN Production.Product AS p
ON pod.ProductID = p.ProductID
JOIN HumanResources.Employee AS e
ON poh.EmployeeID = e.BusinessEntityID
JOIN Person.Person AS per
ON e.BusinessEntityID = per.BusinessEntityID
WHERE per.LastName LIKE @LastName
ORDER BY per.LastName,
per.FirstName;
END
ELSE
BEGIN
SELECT poh.PurchaseOrderID,
poh.OrderDate,
pod.LineTotal,
p.[Name] AS ProductName,
e.JobTitle,
per.LastName + ', ' + per.FirstName AS SalesPerson,
poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
JOIN Purchasing.PurchaseOrderDetail AS pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
JOIN Production.Product AS p
ON pod.ProductID = p.ProductID
JOIN HumanResources.Employee AS e
ON poh.EmployeeID = e.BusinessEntityID
JOIN Person.Person AS per
ON e.BusinessEntityID = per.BusinessEntityID
WHERE per.LastName LIKE @LastName AND
poh.VendorID = @VendorID
ORDER BY per.LastName,
per.FirstName;
END
GO
Using the IF construct breaks the queries in two. Running it with the same set of parameters resulted in a change in
execution time from 1313ms to 267ms, which is a fairly strong improvement. The reads on Purchasing.PurchaseOrderHeader
went up from 44 to 87, which may not be good. But the Purchasing.PurchaseOrderDetail reads went down from 1,539
to 763. Between the reduction in reads and the reduction in performance time, we're looking at a good solution, possibly.
The execution plan is certainly different, as shown in Figure 25-9 .