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 .

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值