Database Reference
In-Depth Information
FROM Purchasing.PurchaseOrderDetail AS pod
JOIN Production.Product AS p
ON p.ProductID = pod.ProductID
GROUP BY p.[Name]
HAVING (SUM(pod.RejectedQty) / SUM(pod.ReceivedQty)) > .08;
SELECT p.[Name] AS ProductName,
SUM(pod.OrderQty) AS OrderQty,
SUM(pod.ReceivedQty) AS ReceivedQty,
SUM(pod.RejectedQty) AS RejectedQty
FROM Purchasing.PurchaseOrderDetail AS pod
JOIN Production.Product AS p
ON p.ProductID = pod.ProductID
WHERE p.[Name] LIKE 'Chain%'
GROUP BY p.[Name];
All three queries use the aggregation function SUM on columns of the PurchaseOrderDetail table. Therefore, you
can create an indexed view to precompute these aggregations and minimize the cost of these complex computations
during query execution.
Here are the number of logical reads performed by these queries to access the appropriate tables:
Table 'Workfile'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 0, logical reads 0
Table 'Product'. Scan count 1, logical reads 6
Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66
CPU time = 0 ms, elapsed time = 128 ms.
Table 'Workfile'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 0, logical reads 0
Table 'Product'. Scan count 1, logical reads 6
Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66
CPU time = 0 ms, elapsed time = 158 ms.
Table 'PurchaseOrderDetail'. Scan count 5, logical reads 894
Table 'Product'. Scan count 1, logical reads 2, physical rea
CPU time = 0 ms, elapsed time = 139 ms.
I'll use the following script to create an indexed view to precompute the costly computations and join the tables:
IF EXISTS ( SELECT *
FROM sys.views
WHERE object_id = OBJECT_ID(N'[Purchasing].[IndexedView]') )
DROP VIEW [Purchasing].[IndexedView];
GO
CREATE VIEW Purchasing.IndexedView
WITH SCHEMABINDING
AS
SELECT pod.ProductID,
SUM(pod.OrderQty) AS OrderQty,
SUM(pod.ReceivedQty) AS ReceivedQty,
SUM(pod.RejectedQty) AS RejectedQty,
COUNT_BIG(*) AS [Count]
 
Search WWH ::




Custom Search