Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 20, 2003

Indexed Views Basics in SQL Server 2000 - Page 4

By Andrew Novick

Putting the New Index to Use



For starters, let's query OrderDetailsXSB and take a look at the plan. This query retrieves the same information as the original query on [Order Details Extended]:



DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
SELECT ProductName, UnitPrice as UnitPri, Quantity as Qty
     , Discount as Disc 
     , CONVERT(numeric(18,2), UnitPrice*Quantity*(1-Discount))
                AS ExtendedPrice
    FROM OrderDetailsXSB
    WHERE OrderID = 10657
GO
(Results)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ProductName                     UnitPri  Qty Disc ExtendedPrice  
------------------------------- -------- --- ---- ---------------
Genen Shouyu                     15.5000  50  0.0          775.00
Jack's New England Clam Chowder   9.6500  24  0.0          231.60
Spegesild                        12.0000  45  0.0          540.00
Zaanse koeken                     9.5000  10  0.0           95.00
Gnocchi di nonna Alice           38.0000  45  0.0         1710.00
Camembert Pierrot                34.0000  30  0.0         1020.00

6 row(s) affected)

Table 'OrderDetailsXSB'. Scan count 1, logical reads 2, physical 
reads 2, read-ahead reads 0.

The results are the same as the first query but the statistics are different. They show only 2 physical reads. That's half the physical reads used by the query without the index. That is the kind of performance gains that can be expected from the addition of indexed views.

Figure 2 shows the query plan for the query and reveals that the indexed view was queried instead of joining the two tables. Adding the index had the desired effect of avoiding the join and reducing IO.

Figure 2 Query Plan of Query on [Order Details SB]

The only fly in the ointment is that the ExtendedPrice column is no longer in the view and has to be added to the query that selects from OrderDetailsXSB. Or does it?

The optimizer can use the new index to improve the performance of any query where it may help out. Let's try our original query on the unaltered view [Order Details Extended] and see what happens:

DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
SELECT ProductName, UnitPrice as UnitPri, Quantity as Qty
     , Discount as Disc, ExtendedPrice 
    FROM [Order Details Extended]
    WHERE OrderID = 10657
GO
(Results)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ProductName                     UnitPri  Qty Disc ExtendedPrice  
------------------------------- -------- --- ---- ---------------
Genen Shouyu                     15.5000  50  0.0          775.00
Jack's New England Clam Chowder   9.6500  24  0.0          231.60
Spegesild                        12.0000  45  0.0          540.00
Zaanse koeken                     9.5000  10  0.0           95.00
Gnocchi di nonna Alice           38.0000  45  0.0         1710.00
Camembert Pierrot                34.0000  30  0.0         1020.00

(6 row(s) affected)

Table 'OrderDetailsXSB'. Scan count 1, logical reads 2, physical
reads 2, read-ahead reads 0.

Figure 3 shows that the plan is the same plan that was used for directly querying OrderDetailsXSB. The IO statistics show that the same benefit was achieved as if OrderDetailsXSB was queried directly.

Figure 3 Query Plan that Uses an Index on a Different View

This is great! It means that the original queries do not have to be changed in order to reap the benefits of indexed views. An application can be left intact, indexed views can be added to the database, and query performance will improve, usually substantially.

Using Indexed Views On Non Enterprise Editions of SQL Server

At the start if the article I mentioned that to use indexed views on SQL Server editions other than Enterprise and Developer, the NOEXPAND query hint must be used. NOEXPAND Specifies that the indexed view is not expanded when the query optimizer processes the query. In effect, the query optimizer treats the view like a table with clustered index.

In order to use NOEXPAND, the view must be named in the query! That means that all the wonderful optimizer magic that I just showed to you in the previous section doesn't happen. Under Enterprise and Developer Editions, the optimizer is able to use the OrderDetailsXSB indexed view to substitute for [Order Details Extended] even though OrderDetailsXSB wasn't in the query. In other editions, it can't. If our example query is going to run under the Standard Edition it must become:

SELECT ProductName, UnitPrice as UnitPri, Quantity as Qty
     , Discount as Disc 
     , CONVERT(numeric(18,2), UnitPrice*Quantity*(1-Discount))
                AS ExtendedPrice
    FROM OrderDetailsXSB  WITH (NOEXPAND)
    WHERE OrderID = 10657

The query must use the WITH (NOEXPAND) hint in the FROM clause. The drawback to using NOEXPAND is that the view must be indexed. If it is not indexed SQL Server issues by SQL Server the message:

Server: Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'OrderDetailsXSB' is invalid.

The bottom line is that to use indexed views on queries with the NOEXPAND hint, the view must be named in the query, which means the view is going to have to always exist in the database. The best way to make this happen is to make views that satisfy the restrictions on indexes and then use them as the basis for the views needed by the application. For example, the alternative to changing the application to query OrderDetailsXSB directly is to define [Order Details Extended] as a view based on OrderDetailsXSB. Then the applications SQL statements don't have to be changed to use the indexed view. In essence, you the DBA/Programmer are doing the work the optimizer would have done. The disadvantage to that is that the optimizer loses its flexibility and won't be able to make the best execution plan based on costs.

Conclusion

Although there are many restrictions on indexed views and they may not be as useful in certain editions of SQL Server, indexed views can provide a very significant improvement in performance. Getting around the restrictions may not be as difficult as it originally appears due to the optimizer's ability to make use of the index even if the query is not directly on the view being queried.

While the improvement in query performance can be substantial, the costs can be large enough to offset any benefits. Maintaining the index on a view has performance costs: it adds overhead to every insert, update or delete on a base table that participates in the view. This limits the applicability of indexed views to situations where data modifications are limited; preferably, where the data modification occurs in batches. Also, don't forget about the extra space consumed by the index. Applications that involve large proportions of decision support and analysis are the best candidates for using indexed views.

References:

Improving Performance with SQL Server 2000 Indexed Views

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/indexvw.asp

» See All Articles by Columnist Andrew Novick



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date