Improve performance with SQLServer2000 indexed view (lower)

server|sqlserver| View | index | Performance using the Index Tuning Wizard

The Index Tuning Wizard also recommends that you use an indexed view in addition to the index of the base table. Use this wizard to improve the ability of an administrator to determine the combination of index and indexed views, thereby optimizing the performance of a typical mixed query performed against a database.

Because the Index Tuning Wizard enforces all the required set options (to ensure that the result set is correct), its indexed view is created successfully. However, these views may not be available if your application's options are not set according to your requirements. Insert, update, or delete operations that are performed on tables that participate in the indexed view definition may fail.


Maintaining indexed views

SQL Server automatically maintains an indexed view, which is similar to maintaining any other index. For normal indexes, each index is connected directly to a single table. By performing each INSERT, update, or DELETE operation on the underlying table, the index is updated accordingly so that the values stored in the index are always consistent with the table.

The maintenance of indexed views is similar to this. However, if the view references more than one table, updating any of those tables requires updating the indexed view. Unlike a normal index, performing a row insert on any one of the participating tables can result in multiple row inserts in the indexed view. The same is true for update and delete operations. Therefore, maintaining an indexed view is more expensive than maintaining a table's index.

In SQL Server 2000, some views can be updated. If a view can be updated, use the INSERT, UPDATE, and DELETE statements to modify the underlying base table directly from the view. Creating an index for a view does not prevent the view from being updated. For more information about updatable views, see Modify data through view in SQL Server online books, SQL Server 2000.

Considerations for maintenance costs

The following points should be considered when designing an indexed view:

An additional storage space is required in the database for indexed views. The result set of an indexed view is physically stored in a database in a way that is similar to a typical table storage space.
SQL Server automatically maintains views. Therefore, any change to the base table on which the view is defined can cause one or more changes to the view index, resulting in an increase in maintenance overhead.
The net performance improvement achieved by a view is the difference between the amount of query execution savings that the view provides and the cost of storing and maintaining the view.

It is relatively simple to estimate the amount of storage space that the view will occupy. Use the "Show Estimated execution plan" tool in SQL Query Analyzer to find the value of the SELECT statement in the view definition. The tool will draw an approximation of the number of rows and row sizes returned by the query. By multiplying the two values, you can estimate the possible size of the view. But this is only an approximation. The actual size of the view index can only be obtained precisely by creating a view index.

From the standpoint of the automated maintenance considerations that SQL Server performs, the ability to "show estimated execution plans" may have an understanding of the impact of this overhead. If you use SQL Query Analyzer to evaluate the statement that modifies the view (an UPDATE statement for the view, an INSERT statement for the base table), SHOWPLAN will include the statement's maintenance operation. Also, consider the cost and the number of times this operation will occur in the production environment, indicating the possible cost of view maintenance.

It is generally recommended that any modifications and updates made to the view or base table should be performed as many batches as possible, rather than individually. This can reduce some of the overhead of view maintenance.


Create an indexed view

The steps required to create an indexed view are inseparable from the successful implementation of the view.

Make sure that the SET options for all existing tables referenced in the view are correct.
Before creating any new tables and views, make sure that the SET options for the session are set correctly.
Make sure that the view definition is OK.
Use the WITH SCHEMABINDING option to create a view.
Creates a unique clustered index of the view.

Use SET options to achieve consistent results

If you enable different SET options when you execute a query, evaluation of the same expression in SQL Server can produce different results. For example, after setting set option Concat_null_yields_null to ON, the value returned by the expression ' abc ' + NULL is NULL. When the concat_null_yieds_null is set to OFF, the expression results in ' abc '. Indexed views require that the values of multiple SET options be fixed to ensure that these views are properly maintained and return consistent results.

The SET option in the following table must be set to the value shown in the required value column whenever the following conditions are present:

An indexed view was created.
Any INSERT, UPDATE, or DELETE operation was performed on any table referenced in the indexed view.
The query optimizer uses an indexed view to generate a query plan.
SET
Option requirements
The value defaults
Server
The value of OLE DB
And
ODBC value DB LIB
The value
Ansi_nulls on Off
Ansi_padding on On
Ansi_warning on Off
ARITHABORT on Off
Concat_null_yields_null on Off
Numeric_roundabort off
QUOTED_IDENTIFIER on Off


If you are using an OLE DB or an ODBC server connection, the only value that must be modified is the ARITHABORT setting. All DB LIB values must be set correctly at the server level or using the SET command for the correct settings from the application using sp_configure. For more information about SET options, see "Using the Options in SQL Server (English)" in SQL Server online books for SQL Server 2000.


Using Deterministic functions

The definition of an indexed view must be deterministic. If all the expressions in the select list and the WHERE and GROUP by clauses are deterministic, the view is deterministic. As long as a deterministic expression is evaluated with a specific set of input values, the same result must be returned. Only deterministic functions can be added to deterministic expressions. For example, DATEADD is a deterministic function, because any given set of variable values is evaluated with its three parameters, always returning the same result. GETDATE is not a deterministic function because it is always called with the same variable, and the value returned after each execution is different. For more information, see "Deterministic and Nondeterministic Functions" in SQL Server online books, which is a 2000.

Even if an expression is deterministic, but if it contains a floating expression, the exact result may depend on the processor architecture or the version of the micro-code. To ensure the integrity of data in SQL Server 2000, such expressions can only be added to non-critical columns of indexed views. A deterministic expression that does not contain a floating expression is called an exact expression. Only an exact deterministic expression can be added to the key column of the indexed view and the WHERE or GROUP by clause.

Use the COLUMNPROPERTY function and the IsDeterministic property to determine whether the view column is deterministic. Use the COLUMNPROPERTY function and the Isprecise property to determine whether the deterministic columns in the view containing schema bindings are accurate. If TRUE, ColumnProperty returns 1, and if FALSE, returns 0, or NULL if the input is invalid (the column is not deterministic). For example, SELECT columnproperty (object_id (' Vdiscount1 '), ' sumdiscountprice ', ' isprecise ') returns 0 because the Sumdiscountprice column references the table Floating column Discount in the order Details. The column sumprice in the same view is both deterministic and accurate.

Note: The view on which this SELECT statement is based can be found in the Example section (view 1).


Other requirements

In addition to the requirements listed in the design guidelines, use SET options for consistent results, and use deterministic functions, you must also meet the following requirements.

Base table Requirements

The base table must set the SET option correctly when it is created, otherwise it cannot be referenced by a view that contains schema bindings.
A table must be referenced by a two-part name (owner. Table name) in the view definition.
function requirements

User-defined functions must be created using the WITH SCHEMABINDING option.
A user-defined function must be referenced by a two-part name (owner. function).
View Requirements

The view must be created with the WITH SCHEMABINDING option.
The view must refer only to base tables in the same database, not to other views.
Syntax restrictions

There are several limitations to the syntax of the view definition. The view definition cannot contain the following contents:

COUNT (*)
ROWSET function
Derived tables
Self-Join
DISTINCT
STDEV, Variance, AVG
float* columns, text columns, ntext columns, image columns
Child query
Full-text predicates (contain, FREETEXT)
SUM of nullable expressions
MIN, MAX
Top
OUTER joins
UNION
Note: Indexed views can contain floating columns, although such columns cannot be included in the cluster index keywords.

GROUP by limit

If you do not use GROUP by, an expression cannot be used in a select list.

If GROUP by is used, the VIEW definition:

Must contain COUNT_BIG (*).
shall not contain having, CUBE or ROLLUP.
These restrictions apply only to indexed view definitions. Queries can use indexed views in their execution plans, even if the indexed view does not conform to these GROUP by restrictions.

Indexing requirements

The user executing the CREATE INDEX statement must be the view owner.
If the view definition contains a GROUP BY clause, a unique cluster index keyword can refer only to the columns specified in the GROUP BY clause.

Example

The example in this section illustrates the use of indexed views in two primary queries (aggregations and joins). It also describes the criteria that the query optimizer uses when determining whether an indexed view is available. For a complete list of these conditions, see how the query optimizer uses indexed views.

Queries are based on the tables in Northwind (database samples provided in SQL Server 2000) and can be written to execute. Before and after creating a view, it is a good idea to use the Show Execution Plan tool in the SQL query optimizer to view the plan selected by the query optimizer. Although the example illustrates how the optimizer chooses the lowest-cost execution plan, the Northwind database sample is too small to reflect performance improvements.

The following query shows how to return two methods of five products with the largest total discount from the Order Details table.

Query 1

SELECT Top 5 ProductID, SUM (unitprice*quantity)-
SUM (unitprice*quantity* (1.00-discount)) as rebate
from [Order Details]
GROUP by ProductID
ORDER BY Rebate DESC

Query 2

SELECT Top 5 ProductID, SUM (unitprice*quantity*discount) as rebate
from [Order Details]
GROUP by ProductID
ORDER BY Rebate DESC

The execution plan selected by the query optimizer contains:

There are an estimated 2,155 rows for the clustered index scan of the order Details table.
Hash matching/aggregation operator, which puts the selected row into a hash table based on the GROUP by column, and then calculates the SUM aggregation for each row.
The top 5 sort operator based on the ORDER BY clause.
View 1

Adding an indexed view that includes the aggregation required for the rebate column changes the query execution plan for query 1. On a large table of millions of rows, the query's performance will also be significantly improved.

CREATE VIEW Vdiscount1 with schemabinding
As
SELECT SUM (unitprice*quantity) as Sumprice,
SUM (unitprice*quantity* (1.00-discount))
As Sumdiscountprice, COUNT_BIG (*) as COUNT, ProductID
FROM dbo. [Order Details]
GROUP by ProductID
Go
CREATE UNIQUE CLUSTERED INDEX vdiscountind on Vdiscount1 (ProductID)

The execution plan for the first query shows that the Vdiscount1 view is used by the query optimizer. However, because the view does not contain SUM (unitprice*quantity*discount) aggregations, it is not used by the second query. You can create another indexed view that satisfies both of these queries.

View 2

CREATE VIEW Vdiscount2 with schemabinding
As
SELECT SUM (unitprice*quantity) as Sumprice,
SUM (unitprice*quantity* (1.00-discount)) as Sumdiscountprice,
SUM (Unitprice*quantity*discount) as SumDiscountPrice2, COUNT_BIG (*)
As Count, ProductID
FROM dbo. [Order Details]
GROUP by ProductID
Go
CREATE UNIQUE CLUSTERED INDEX vdiscountind on Vdiscount2 (ProductID)

With this indexed view, the query execution plan for two queries now contains:

Clustered index Scan for Vdiscount2 view with an estimated 77 rows
Top 5 Sort functions based on ORDER BY clause
The query optimizer chooses this view because it provides the lowest execution cost, although the view is not referenced in the query.

Query 3

Query 3 is similar to the previous query, except that the ProductID has been replaced by OrderID, and the column is not included in the view definition. This violates the condition that all expressions in the query selection list must be able to derive from the view selection list of the table that is not included in the view definition.

SELECT Top 3 OrderID, SUM (unitprice*quantity*discount) orderrebate
FROM dbo. [Order Details]
GROUP by OrderID
ORDER BY orderrebate Desc

Requires a separate indexed view to satisfy the query. Vdiscount2 can be modified to include OrderID, but the generated view will have the same number of rows as the original table, so the performance provided will not be higher than the performance provided by using the base table.

Query 4

This query generates the average price per product.

SELECT ProductName, OD. ProductID,
AVG (OD. unitprice* (1.00-discount)) as Avgprice, SUM (OD. Quantity) as Units
from [order Details] OD, Products P
WHERE od. Productid=p.productid
GROUP by ProductName, OD. ProductID

The definition of an indexed view cannot include complex aggregations (for example, STDEV, Variance, AVG), but if the indexed view includes several simple aggregate functions that combine to perform complex aggregations, it can be used to execute queries that contain AVG.

View 3

The indexed view contains the simple aggregate functions required to perform the AVG function. When you execute Query 4 o'clock after you create view 3, the execution plan displays the view that is being used. The optimizer can derive the AVG expression from the view's simple aggregation column price and Count.

CREATE VIEW View3 with schemabinding
As
SELECT ProductID, SUM (unitprice* (1.00-discount)) as Price,
COUNT_BIG (*) as Count, SUM (Quantity) as Units
FROM dbo. [Order Details]
GROUP by ProductID
Go
CREATE UNIQUE CLUSTERED INDEX iv3 on VIEW3 (ProductID)

Query 5

The query is the same as query 4, except that it includes an additional search condition. View 3 will also be used for this query, even if the additional search condition refers only to columns that are not included in a table in the view definition.

SELECT ProductName, OD. ProductID, AVG (OD. unitprice* (1.00-discount))
As Avgprice, SUM (OD. Quantity) as Units
from [order Details] as OD, products as P
WHERE od. Productid=p.productid
and p.productname like '%tofu% '
GROUP by ProductName, OD. ProductID

Query 6

The query optimizer cannot use view 3 for this query. Additional search conditions OD. UNITPRICE>10 contains columns from a table within the view definition, and the column does not appear in the GROUP by list, and the search predicate does not come out of the view definition.

SELECT ProductName, OD. ProductID, AVG (OD. unitprice* (1.00-discount))
As Avgprice, SUM (OD. Quantity) as Units
from [order Details] OD, Products P
WHERE od. ProductID = P.productid
and OD. UnitPrice > 10
GROUP by ProductName, OD. ProductID

Query 7

Instead, the query optimizer can use View 3 for query 7 because of the new search condition OD. The columns defined in ProductID in (1,2,13,41) are included in the GROUP by clause within the view definition.

SELECT ProductName, OD. ProductID, AVG (OD. unitprice* (1.00-discount))
As Avgprice, SUM (OD. Quantity) as Units
from [order Details] as OD, products as P
WHERE od. ProductID = P.productid
and OD. ProductID in (1,2,13,41)
GROUP by ProductName, OD. ProductID

View 4

The view includes column OD in the view definition. Discount, you can meet the conditions of query 6.

CREATE VIEW View4 with schemabinding
As
SELECT ProductName, OD. ProductID, SUM (OD. unitprice* (1.00-discount))
As Avgprice, SUM (OD. Quantity) as Units, COUNT_BIG (*) as COUNT
FROM dbo. [Order Details] As OD, dbo. Products as P
WHERE od. ProductID = P.productid
and OD. UnitPrice > 10
GROUP by ProductName, OD. ProductID
Go
CREATE UNIQUE CLUSTERED INDEX vdiscountind on View4 (ProductName, ProductID)

Query 8

The same index of view 4 will also be used for a query that adds a join to the table Orders. The query meets the following criteria: The table listed in the query from clause is a superset of the tables in the FROM clause of the indexed view.

SELECT ProductName, OD. ProductID, AVG (OD. unitprice* (1.00-discount))
As Avgprice, SUM (OD. Quantity) as Units
FROM dbo. [Order Details] As OD, dbo. Products as P, dbo. Orders as O
WHERE od. ProductID = P.productid and O.orderid = od. OrderID
and OD. UnitPrice > 10
GROUP by ProductName, OD. ProductID

The last two queries are variants of query 8. Each variant violates an optimizer condition, so unlike query 8, view 4 cannot be used.

Query 8a

Because the UnitPrice > 10 in the View definition does not match the WHERE clause between UnitPrice > 25 in the query, q8a cannot use indexed views. The query search condition predicate must be a superset of the search condition predicate in the view definition.

SELECT ProductName, OD. ProductID, AVG (OD. unitprice* (1.00-discount)) Avgprice,
SUM (OD. Quantity) as Units
FROM dbo. [Order Details] As OD, dbo. Products as P, dbo. Orders as O
WHERE od. ProductID = P.productid and O.orderid = od. OrderID
and OD. UnitPrice > 25
GROUP by ProductName, OD. ProductID

Query 8b

Note that the table Orders did not participate in the definition of the indexed view V4. However, adding a predicate in the table prohibits the use of indexed views because the added verb may eliminate other rows in the aggregation, as shown in Query 8b.

SELECT ProductName, OD. ProductID, AVG (OD. unitprice* (1.00-discount))
As Avgprice, SUM (OD. Quantity) as Units
FROM dbo. [Order Details] As OD, dbo. Products as P, dbo. Orders as O
WHERE od. ProductID = P.productid and O.orderid = od. OrderID
and OD. UnitPrice > 10
and o.orderdate > ' 01/01/1998 '
GROUP by ProductName, OD. ProductID


For more information

The Microsoft SQL Server 2000 online book contains detailed information about the indexed view. For additional information, see the following resources:

Microsoft SQL Server Web site (in English).
Microsoft SQL Server Developer Center (English).
SQL Server Magazine (English).
Microsoft.public.sqlserver.server and Microsoft.public.sqlserver.datawarehouse newsgroups, whose site is: news:// News.microsoft.com (English).
Microsoft's formal course on SQL Server. For the most recent course information, see the Microsoft Training and Services site (in English).