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.