Database Reference
In-Depth Information
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 = COALESCE(@VendorID, poh.VendorID)
ORDER BY per.LastName,
per.FirstName;
GO
Once these procedures are created, you can execute them using the following scripts:
EXEC dbo.PurchaseOrderBySalesPersonName
@LastName = 'Hill%';
GO
EXEC dbo.ShoppingCart
@ShoppingCartID = '20621';
GO
EXEC dbo.ProductBySalesOrder
@SalesOrderID = 43867;
GO
EXEC dbo.PersonByFirstName
@FirstName = 'Gretchen';
GO
EXEC dbo.ProductTransactionsSinceDate
@LatestDate = '9/1/2004',
@ProductName = 'Hex Nut%';
GO
EXEC dbo.PurchaseOrderBySalesPersonName
@LastName = 'Hill%',
@VendorID = 1496;
GO
This is an extremely simplistic workload that just illustrates the process. You're going to see hundreds and
thousands of additional calls in a typical system. As simple as it is, however, this sample workload consists of the
different types of queries you usually execute on SQL Server.
•
Queries using aggregate functions
•
Point queries that retrieve only one row or a small number of rows
•
Queries joining multiple tables
•
Queries retrieving a narrow range of rows
•
Queries performing additional result set processing, such as providing a sorted output
The first optimization step is to identify the worst-performing queries, as explained in the next section.
