Updating indexed views - who pays?

[teddysnips@hotmail.com]

This from a SQL Server manual:

"Complex queries, however, such as those in decision support systems,
can reference large numbers of rows in base tables and aggregate large
amounts of information into relatively concise aggregates (such as sums
or averages). SQL Server 2000 supports creating a clustered index on a
view that implements such a complex
query. When the CREATE INDEX statement is executed, the result set of
the view SELECT is stored permanently in the database. Future SQL
statements that reference the view will have substantially better
response times. Modifications to the base data are automatically
reflected in the view."

My question arises from the last sentence. At what point are the views
updated with the new data? If I am running a transaction that updates
some dependent tables, is there a performance impact while the indexed
views are updated?

Jess Askin.

[--CELKO--]

Re: Updating indexed views - who pays?

This is a qustion for Kalen, since she does internals. But I seem to
remember that the indexes will be re-built on the first invocation of
the VIEW after the base tables have changed, and not while the base
tables are being changed.

[Gert-Jan Strik]

Re: Updating indexed views - who pays?

An indexed view is updated within the transaction that modifies the
relevant base table(s). In other words, the indexed view is updated
immediately.

So yes, the performance of inserts/updates/deletes will be influenced.
If it is a narrow view (few columns with few bytes), then the
performance hit would be comparable with the addition of a 'regular'
index to the base table.

HTH,
Gert-Jan


teddysnips@hotm ail.com wrote:[color=blue]
>
> This from a SQL Server manual:
>
> "Complex queries, however, such as those in decision support systems,
> can reference large numbers of rows in base tables and aggregate large
> amounts of information into relatively concise aggregates (such as sums
> or averages). SQL Server 2000 supports creating a clustered index on a
> view that implements such a complex
> query. When the CREATE INDEX statement is executed, the result set of
> the view SELECT is stored permanently in the database. Future SQL
> statements that reference the view will have substantially better
> response times. Modifications to the base data are automatically
> reflected in the view."
>
> My question arises from the last sentence. At what point are the views
> updated with the new data? If I am running a transaction that updates
> some dependent tables, is there a performance impact while the indexed
> views are updated?
>
> Jess Askin.[/color]

[Hugo Kornelis]

Re: Updating indexed views - who pays?

On 19 May 2005 09:17:02 -0700, --CELKO-- wrote:
[color=blue]
>This is a qustion for Kalen, since she does internals. But I seem to
>remember that the indexes will be re-built on the first invocation of
>the VIEW after the base tables have changed, and not while the base
>tables are being changed.[/color]

Hi Joe,

You remember wrong. The indexed view is updated at each modification to
the base table(s).

However, the view is not completely re-executed (that would be a major
performance killer!). Instead, SQL Server uses some nifty logic to speed
up the update to the view. For example, if the view has a SUM(...) and a
COUNT(...) column, then the "new" values are computed by adding the SUM
and COUNT of the new rows and subtracting the SUM and COUNT of the old
roms from the "old" values in the indexed view.

With this in mind, you'll suddenly have a lot better understanding for
the list of limitations imposed on indexed views :-)


To the OP: there is a performance impact on modifications to the base
table, but it will be a small impact in most cases. You'll have to test
each specific case to be sure, though!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)