See performance gains by using indexed views in SQL Server

http://www.zdnetasia.com/see-performance-gains-by-using-indexed-views-in-sql-server-61992919.htm

 

A view is a saved T-SQL query in SQL Server. The view definition is stored by SQL Server so that it can be used as a virtual table to simplify queries and add a layer of security to your base tables; however, it does not take up any space in the database. In fact, a view really doesn't do anything until you query it.

Indexed views
In SQL Server 2000 and 2005, you have the ability to add indexes to views. But, if a view is just a stored query definition in the database with no data of its own until it is run, how can you create an index on that definition? Well, it's tricky.

An indexed view is a view that has been materialized or stored in the database. The index that is created on the view is stored and updated by the database engine as the underlying table is updated. Indexed views work great for situations where your result set returns a lot of rows and needs to be aggregated. However, it is not a good idea to create an indexed view on tables that are highly transactional. This is because the database engine must maintain the index on the view as the base table data is updated, which can degrade transaction performance.

To create an index on a view, the view definition must adhere to a certain set of conditions and session settings, along with the requirement to tie base tables to the view definition. If you determine that your view meets the criteria specifics (which I will discuss later in this article), the first index you must create on your view is a unique clustered one. The first one created must be on a unique set of columns and clustered so that the index is materialized.

Listing A shows an example of how an indexed view is created. The script will create the SalesHistory table and load data into it.

The following code will create a view to summarize the data in the table:

CREATE VIEW vw_salesbyproduct

AS

      SELECT

            Product, 

            COUNT_BIG(*) as ProductCount, 

            SuM(SalePrice) as TotalSales

      FROM  dbo.SalesHistory

      GROUP BY Product

It doesn't take any time to create the view because it is only a T-SQL query definition. Once the view is created, you can run queries against it like you would a table.

SELECT Product, TotalSales, ProductCount 



FROM vw_SalesByProduct



WHERE product = 'Computer'

If you set the option in either SQL Server Management Studio or Query Analyzer to see the Execution Plan of the query, you will notice that the above query uses a table scan to find the aggregated result based upon the Computer product. Table scans are typically used in situations where there is no index on the data, so the entire result set is scanned through to find the requested results.

With a few simple changes, you can change the view so that you are able to add an index to it to improve search performance. First, you need to make sure the following session settings are set:

SET ANSI_NULLS ON



GO



SET ANSI_PADDING ON



GO



SET ANSI_WARNINGS ON



GO



SET CONCAT_NULL_YIELDS_NULL ON

GO



SET QUOTED_IDENTIFIER ON



GO



SET NUMERIC_ROUNDABORT OFF



GO

Now you can create our view. To make things easier, I am going to create a brand new view.

CREATE VIEW dbo.vw_SalesByProduct_Indexed



WITH SCHEMABINDING



AS

      SELECT 

            Product, 

            COUNT_BIG(*) AS ProductCount, 

            SUM(ISNULL(SalePrice,0)) AS TotalSales

      FROM dbo.SalesHistory

      GROUP BY Product



GO

The script below creates the index on our view:

CREATE UNIQUE CLUSTERED INDEX

idx_SalesView ON vw_SalesByProduct_Indexed(Product)

To show that an index has been created on the view and that it does take up space in the database, run the following script to find out how many rows are in the clustered index and how much space the view takes up.

EXECUTE sp_spaceused 'vw_SalesByProduct_Indexed'

The SELECT statement below is the same statement as before, except this time it performs a clustered index seek, which is typically very fast.

SELECT 

      Product, TotalSales, ProductCount 



FROM vw_SalesByProduct_Indexed



WHERE Product = 'Computer'

Don't forget performance testing
Indexed views are great when you use them in the right situations because they can drastically improve the performance of queries. However, due to the added performance gain from the clustered index, the database engine must maintain that index throughout all of the transactions occurring on the base tables of the view. Because of this tradeoff, creating an index view may or may not be of benefit to your system. The best way to determine this is through thorough performance testing.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com .