Improve query performance for aggregated and joined data with Indexed Views, using SQL Server 2012

In this session will describe how to improve your query performance by using SQL Server 2012 Indexed Views. We'll provide a brief overview of Indexed Views, review performance gains achieved from Indexed Views, applications that benefit using Indexed Views and finally illustrate an example Indexed Views usage.

NOTE: This session uses the AdventureWorksDW2012 database for the example script.

What are Indexed Views?

An Indexed View is the result of storing joined and aggregated data to a view with a unique clustered index. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The Indexed View does not need to be referenced in the query for the Query Optimizer to use it during execution - providing the query structurally matches the view.

What are the Performance Gains?

You may already know how traditional indexes help to improve query performance on tables. Using Indexed Views provided enhanced performance in the following ways:

• Aggreggations are precomputed and stored in the index
• Stored prejoined tables
• Stored coalesced data joins or aggregations

Which Data Areas benefit from using Indexed Views?

• Data Warehouse
• Data Marts
• Decision Suport System (DSS)
• Online Analytical Processing (OLAP) stores
• Data Mining

Queries that contain multiple aggregations and joins are great candidates for Indexed Views. Other candidates are queries which may take a large amount of time to render and the demand for quick response from business units are high.

NOTE: There are many limitations, restrictions and considerations while working with Indexed Views. You should refer to Create Indexed Views - SQL Server 2012 for more information.

Example

For example, let's say you have a requirement to show aggregated data for Sales Amount and Total Costs for all Products in the relational data warehouse. Your query would look something like below:

SELECT

ProductKey,

SUM(UnitPrice*OrderQuantity)ASTotalSalesAmount,

SUM(TotalProductCost+TaxAmt+Freight) AS TotalCosts,

COUNT_BIG(*) AS NumberOfRows

FROM [dbo].[FactInternetSales]

GROUPBY  ProductKey

GO

Run the query with the Execution Plan. You should see results similar to below. 

Now let's create an Indexed View with the same query structure. We'll begin by ensuring we have the correct options set for our session, prior to executing our command. Enter the following SET Statements in your Query Designer:

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

Now, let's define our Indexed View.

CREATE VIEW dbo.vProductSalesCostsByYear

-- SCHEMABINDING binds the view to the schema of the underlying table or tables.

WITH SCHEMABINDING AS

SELECT

ProductKey,

SUM(UnitPrice*OrderQuantity) AS TotalSalesAmount,

SUM(TotalProductCost+TaxAmt+Freight) AS TotalCosts,

COUNT_BIG(*)ASNumberOfRows

FROM [dbo].[FactInternetSales] 

GROUP BY  ProductKey

GO

-- Create clustered index on view

CREATE UNIQUE CLUSTERED INDEX CLU_INDEX1 ON

       dbo.vProductSalesCostsByYear (ProductKey)

GO

Execute the script above to create the Indexed View. You should receieve the following:

Command(s) completed successfully.

Now run the following t-sql with the Execution Plan:

SELECT

ProductKey,

SUM(UnitPrice * OrderQuantity) AS TotalSalesAmount,

SUM(TotalProductCost + TaxAmt + Freight) AS TotalCosts,

COUNT_BIG(*) AS NumberOfRows

FROM [dbo].[FactInternetSales] 

GROUP BY  ProductKey

GO

Without Indexed View		With Indexed View

Review the Execution Plan results and notice the difference in Estimated I/O Costs and CPU Costs.

If you're working with large amounts joined and aggregated data, Indexed Views may help improve your overall performance.

Cheers!