Monday, May 30, 2016

Using Indexed View to improve query performance


select concat(c.FirstName,' ',c.LastName) CustomerName,subtotal.TotalAmount
 from
DimCustomer c left outer join
(
select p.EnglishProductName, s.CustomerKey,sum(s.SalesAmount) TotalAmount
from DimProduct p join FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
) subtotal
on c.CustomerKey=subtotal.CustomerKey
order by CustomerName
OPTION (MAXDOP 1);



(58922 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCustomer'. Scan count 1, logical reads 975, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactInternetSales'. Scan count 1, logical reads 1234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimProduct'. Scan count 1, logical reads 251, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


--Set the options to support indexed views. 
--These are default SQL Server settings as well
-- But not default for ODBC/OLEDB
-- https://msdn.microsoft.com/en-sg/library/ms191432.aspx

SET NUMERIC_ROUNDABORT OFF; 
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, 
    QUOTED_IDENTIFIER, ANSI_NULLS ON; 
GO 

IF OBJECT_ID ('vCustProdSales', 'view') IS NOT NULL 
drop view vCustProdSales
GO

CREATE VIEW vCustProdSales
WITH SCHEMABINDING 
AS 
select p.EnglishProductName, s.CustomerKey,sum(s.SalesAmount) TotalAmount, count_big(*) Sales_Count
from dbo.DimProduct p join dbo.FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
GO 
--Create an index on the view. 
CREATE UNIQUE CLUSTERED INDEX IDX_vCustProdSales
    ON vCustProdSales (EnglishProductName, CustomerKey); 
GO 


select concat(c.FirstName,' ',c.LastName) CustomerName,subtotal.TotalAmount
 from
DimCustomer c left outer join
(
select p.EnglishProductName, s.CustomerKey,sum(s.SalesAmount) TotalAmount
from DimProduct p join FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
) subtotal
on c.CustomerKey=subtotal.CustomerKey
order by CustomerName
OPTION (MAXDOP 1);




(58922 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCustomer'. Scan count 1, logical reads 975, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'vCustProdSales'. Scan count 1, logical reads 530, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.