Database Reference
In-Depth Information
The new execution plan consists of index seeks and nested loops joins. There is a sort operator, the second
costliest in the plan, ordering the data by
LastName
and
FirstName
. Getting this to be taken care of by the retrieval
process might help to improve performance, but I've had a fairly successful tuning to this point, so I'll leave it as is
for now.
One additional consideration should be made for the split query. When the optimizer processes a query like this,
both statements will be optimized for the parameter values passed in. Because of this, you may see bad execution
plans, especially for the second query that uses the
VendorID
for filtering, because of parameter sniffing gone bad.
To avoid that situation, one additional tuning effort should be made.
Creating a Wrapper Procedure
Because you've created two paths within the procedure in order to accommodate the different mechanisms of
querying the data, you have the potential for getting bad parameter sniffing because both paths will be compiled,
regardless of the parameters passed. One mechanism around this is to run the procedure you have into a wrapper
procedure. But first, you have to create two new procedures, one for each query like this:
CREATE PROCEDURE dbo.PurchaseOrderByLastName @LastName NVARCHAR(50)
AS
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
ORDER BY per.LastName,
per.FirstName;
GO
CREATE PROCEDURE dbo.PurchaseOrderByLastNameVendor
@LastName NVARCHAR(50),
@VendorID INT
AS
SELECT poh.PurchaseOrderID,
poh.OrderDate,
pod.LineTotal,
p.[Name] AS ProductName,
e.JobTitle,
per.LastName + ', ' + per.FirstName AS SalesPerson,
poh.VendorID
