Very Computer

Indexed view can contain (many) joins (many underlying tables), so
recalculation of the view may take considerable time...

Indexed view is (physically) a (system controlled) temp table (like
any index by the way), that is being repopulated (and reindexed) each
time, when one (any) of the underlying tables are/is updated. This
repopulation may take MUCH longer time that actual update of the
underlying table.

First I want to understand basic things about indexed views: HOW they
are recalculated?

Lets assume data one of the underlying tables is updated by one
statement, as a result 1 mln rows end up changed. I am sure the
optimizer is smart enough to recalculate indexed view only once (so
recalculation is being done as a result of a statement update, not
each separate row update :-) Thats understandable.

But what if update is being done inside a cursor loop - one row at a
time? Then the indexed view MUST be recalculated after each row update
- i.e. number of times the row level update inside the loop is
implemented - still 1 mln times...
But it will break, no doubt about it (without protecting algorythm)

So the internal mechanism should work even smarter - there should be
dirty flag against change of any underlying table, all right, but
there should also be some time delay (or other principle), so that the
 (view contents) recalculation requirement should not line up in
endless line...

These are my phantasies on the subject. Is there any white paper, that
would descibe this? Thanks in advance... (I can not afford to buy
books, although I understand "Inside SQL Server 2000" should write
about the stuff).

Alexei

Alexei,

The internal mechanisms of how SQL Server 2000 maintains indexed views is
apparently considered by Microsoft to be proprietary information. I know of
no published information on it. I know for a fact that "Inside Microsoft SQL
Server 2000" does not address how indexed views are maintained. In fact, I
once had an online conversation with Kalen Delaney, author of "Inside
Microsoft SQL Server 2000", and Ms. Delaney mentioned that she was not
knowledgeable, at least at the time of our conversation, regarding the
internal mechanisms of how SQL Server maintains indexed views.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> Indexed view can contain (many) joins (many underlying tables), so
> recalculation of the view may take considerable time...

> Indexed view is (physically) a (system controlled) temp table (like
> any index by the way), that is being repopulated (and reindexed) each
> time, when one (any) of the underlying tables are/is updated. This
> repopulation may take MUCH longer time that actual update of the
> underlying table.

> First I want to understand basic things about indexed views: HOW they
> are recalculated?

> Lets assume data one of the underlying tables is updated by one
> statement, as a result 1 mln rows end up changed. I am sure the
> optimizer is smart enough to recalculate indexed view only once (so
> recalculation is being done as a result of a statement update, not
> each separate row update :-) Thats understandable.

> But what if update is being done inside a cursor loop - one row at a
> time? Then the indexed view MUST be recalculated after each row update
> - i.e. number of times the row level update inside the loop is
> implemented - still 1 mln times...
> But it will break, no doubt about it (without protecting algorythm)

> So the internal mechanism should work even smarter - there should be
> dirty flag against change of any underlying table, all right, but
> there should also be some time delay (or other principle), so that the
>  (view contents) recalculation requirement should not line up in
> endless line...

> These are my phantasies on the subject. Is there any white paper, that
> would descibe this? Thanks in advance... (I can not afford to buy
> books, although I understand "Inside SQL Server 2000" should write
> about the stuff).

> Alexei

Alexei,

I have completely different assumptions about the way indexed views are
implemented in SQL-Server 2000. I think they are never recalculated, but
updated with every transaction on the base tables. This could be done
with 'hidden triggers'.

This implementation has the performance benefit of never having to
recalculate the materialized view. It has the disadvantage that many
types of queries are not possible. This could explain why it is not
possible to use TOP, outer joins, DISTINCT and 'complex' aggregations
like AVG, SUM, etc. It would also explain why indexed views with
aggregate require the COUNT_BIG(*) aggregate.

My 5 cents,
Gert-Jan

The truth is much simpler (than I envisaged in my original post):

there is no need to update the population of materialized temporary
"table", that corresponds to the indexed view on data change.

When underlying tables change, then nothing happens with the view (no
recalculation takes place).

The indexed view just gets one dirty flag set up, indicating that "at
least one underlying table had been modified".

So on FIRST select from the indexed view, it is being REPOPULATED, and
(calculated data) will be reused as long, as the data in underlying
tables not changed again.

So materialized view NEVER produces any gain on its first use (i.e. on
very first select after data change), because it repopulates itself
into a temporary table (i.e. behaves like a normal view, but the
normal view output is not
memorized).

So it means that in transaction (OLTP) world indexed views will never
be used (i.e. will behave like usual views)- because data in tables
changes all the time.

Now everything is clear. At least MS might have mentioned this fact
(indexed views are slow on first use) in MSDB.

(technically they may have implemented a background process that would
have be filling the "body" of the materialized views, when the CPU is
not active for a while and a dirty flag raised, but I am sure they
have not done it,
otherwise this would have been described everywhere).

Well, but in this case, there is not much difference between indexed
views and regular views, since all joins and result sets from  the
views being run get stuck in TEMPDB anyway, so they are reused (I am
sure in many scenarios the  quiery optimizer is smart enough to reuse
them, because second invocation of the view is practically always
instant, even if where conditions and sorting are changed for the
output of the view- so they are reused from TEMPDB, not
from ram memory cache).

So all this means that the indexed views are practical only for DSS,
data marts and datawarehousing world, and have no practical use for
data retrieval in OLTP world.

to: Gert-Yan Strik:

Quote:>I think they are never recalculated, but
>updated with every transaction on the base tables. This could be done
>with 'hidden triggers'.

If a indexed view is based upon join of several tables, then
straightforward update is not possible, since some of the rows (in
underlying table) will satisfy the join conditions (and may be
updated),
some will not safisfy the join conditions (and there is nothing to
update in the materialized view), so the join has to be repeated.

Such "update" would not be possible  for aggregations, since we have
to make a join and an aggregation on each (underlying table) row
update. So one row is changed, but millions of other rows (for long
tables) will HAVE to be used for calculating aggregation - no way to
bypass it.

Quote:>This could explain why it is not
>possible to use TOP, outer joins, DISTINCT and 'complex' aggregations
>like AVG, SUM, etc. It would also explain why indexed views with
>aggregate require the COUNT_BIG(*) aggregate.

It IS possible to use sum, count, avg (see MSDN: "Designing an Indexed
View"):

"Not only can this view satisfy queries that directly reference the
view columns, it can also be used to satisfy queries that query the
base table and contain expressions such as SUM(Colx), COUNT_BIG(Colx),
COUNT(Colx), and AVG(Colx). Al such queries will be faster because
they only have to retrieve the small number of rows in the view rather
than reading the full number of rows from the base tables."
end MSDN quote

Alexei,

I have no data to support your theory. However, when I experimented with
an indexed view that had an aggregate, the CPU and I/O statistics showed
that the view was not repopulated on the fly.

FYI: an indexed view is per definition a materialized view. Otherwise it
is of no use! So then, the only remaining question is how it is
maintained. Repopulating at the first request (SELECT) would not be
efficient...

Gert-Jan

<snip>

Quote:> .... However, when I experimented with
> an indexed view that had an aggregate, the CPU and I/O statistics showed
> that the view was not repopulated on the fly.

That is precisely what I am saying: the materialized view is NOT
updated on the fly, there is no maintenance/update/repopulation
mechanism in place, until first selection from the view is being done
(i.e. temp.table stays empty, data stays exclusively in underlying
tables).

Quote:> FYI: an indexed view is per definition a materialized view. Otherwise it
> is of no use!

Not exactly so, the second and subsequent uses of the view ARE using
indexes and join data population (for static underlying data).

Quote:>So then, the only remaining question is how it is
> maintained. Repopulating at the first request (SELECT) would not be
> efficient...

Yes, the existing implementation is not efficient, on the first use
the indexed view is exactly as slow, as the usual view (all data still
sits in underlying tables).
Efficient implementation is just not done.

I do not mean to blame  MS for this, because "efficient"
implementation (i.e. population of the body  with data in the
background mode) would not be robust. I wonder how this is done in
Oracle.

Alexei

Alexei,

I don't think we understand each other.

Let's take a batch as an example.

CREATE TABLE MainTable
(Status char(10) not null
,Total  int      not null
)

CREATE VIEW IView AS
SELECT Status, SUM(Total) AS GrandTotal, COUNT_BIG(*) AS CountBig
FROM MainTable
GROUP BY Status

-- Clustered Index creation
CREATE UNIQUE CLUSTERED INDEX IX_IView_Status ON IView(Status)

-- Inserts 1
INSERT INTO MainTable VALUES ('Test',3)
INSERT INTO MainTable VALUES ('Test',8)
INSERT INTO MainTable VALUES ('Finished',2)

-- Selection 1
SELECT Status, GrandTotal
FROM IView

-- Selection 2
SELECT Status, GrandTotal
FROM IView

INSERT INTO MainTable VALUES ('Finished',15)

-- Selection 3
SELECT Status, GrandTotal
FROM IView

Okay, now we have a testscript. If I understand you correctly, you are
saying that:
a) when Selection 1 is executed, SQL-Server will populate the indexed
view in tempdb, and the query will select from this materialized table.
b) when Selection 2 is executed, the materialized table is still in
tempdb and will be reused
c) when Selection 3 is executed, the tempdb results will be dropped, and
the indexed view will be materialized again (from scratch)

This is definitely not how I think SQL-Server indexed views work. It
also does not match the findings of my experiments. Why I think is
happening is:
a) When the clustered index is created, the indexed view is materialized
(on the specified filegroup, not in tempdb)
b) For each INSERT statement, the indexed view is updated. So these
inserts have become twice as heavy, because both the table and the
indexed view have to be updated
c) When Selection 1 is executed, SQL-Server can simply select from the
materialized view (the indexed view).

And that is why indexed views can improve SELECT performance quite a
lot, but it hurts INSERT/UPDATE/DELETE performance.

Gert-Jan

Vow, Gert-Jan,

testing shows that you proved to be absolutely right in each and every
respect!

1) indexed view table (and index body, defined on the view) are
repopulated automatically after each insert into the underlying table.

2) the underlying table is completely locked (even for selects) for
duration of all processes, connected with population of both table and
indexed view structures (i.e. even attempt to select from table will
wait for completion of repopulation of the index(es) on the view, not
only update of the table itself).

So I was not right: indexed view (i.e. index body) stays always
populated, the process is automatic and reacts on any change in
underlying table(s).
I.e. the first select from indexed view takes the same time, as
subsequent.

Summary: indexed views are great for reporting, DSS, data marts, but
awfull for OLTP, speed of table update,insert and select will be bad
(any attempt to SELECT from tables will be delayed until repopulation
of all indexes in the views is completed) (that is if we do not
specify dirty read hints in select).

I have modified your script:

Step one:

CREATE TABLE MainTable
(Status char(10) not null
,Total  int      not null
)

Step two: create secondary table (will be used in data population):
select * into mtcopy from maintable  where 1=2

-- populate secondary table:
INSERT INTO mtcopy VALUES ('Test',1)
INSERT INTO mtcopy VALUES ('Test',2)
INSERT INTO mtcopy VALUES ('Test',3)
INSERT INTO mtcopy VALUES ('Test',4)
INSERT INTO mtcopy VALUES ('Test',5)
INSERT INTO mtcopy VALUES ('Test',6)
INSERT INTO mtcopy VALUES ('Test',7)
INSERT INTO mtcopy VALUES ('Test',8)
INSERT INTO mtcopy VALUES ('Test',9)
INSERT INTO mtcopy VALUES ('Finished',99)

Step 3: create script for population of the mainTable with 100,000
rows at a time:
-- we use cross-product from 5 named instances of the secondary table
-- 10 in power of 5 = 100,000 rows:

SET STATISTICS IO ON
go
insert into maintable
 select m1.status, m1.total from
mtcopy m1, mtcopy m2, mtcopy m3, mtcopy m4 , mtcopy m5

Step 4: do population of mainTable (indexed view does not exist yet):
Results:
Table 'MainTable'. Scan count 0, logical reads 100001, physical reads
0, read-ahead reads 0.
Table 'mtcopy'. Scan count 5, logical reads 5, physical reads 0,
read-ahead reads 0.

(100000 row(s) affected)
time: 15 seconds.

Populate second time: 15 seconds.

Step 5: Define view:
CREATE VIEW IView with SCHEMABINDING
AS
SELECT Status, SUM(Total) AS GrandTotal, COUNT_BIG(*) AS CountBig
FROM dbo.MainTable
GROUP BY Status

Step 6: repeat population with 100,000 rows.
Result: time 15 seconds, same statistics.
That is understandable, the view exists, but it does not have any
indexes,
no body yet.

Step 7: create index on view:
CREATE UNIQUE CLUSTERED INDEX IX_IView_Status ON IView(Status)
Takes 10 seconds ("the body" of the indexed view is being created).

Step 8: add 100,000 rows: results:
Table 'IView'. Scan count 3, logical reads 10, physical reads 0,
read-ahead reads 0.
Table 'MainTable'. Scan count 0, logical reads 193781, physical reads
0, read-ahead reads 0.
Table 'mtcopy'. Scan count 5, logical reads 5, physical reads 0,
read-ahead reads 0.

(100000 row(s) affected)

Time: 31 seconds.

Now finally: both the underlying table AND the index body of the view
had been repopulated (15 seconds for each=total 30 seconds).
We see that the view is being treated like a "Table IView".

Step 9: select * from iview
results:
Status     GrandTotal  CountBig            
---------- ----------- --------------------
Finished   3960000     40000
Test       1800000     360000

(2 row(s) affected)
time: 12 seconds
Index is not used, because we did not specified the hint. Indexed view
was used like regular view, 12 seconds was used for aggregations
(400,000 rows for now).

Step 10: SELECT Status, GrandTotal FROM IView  with (NOEXPAND)
Results: the same as previous, time 0 seconds.
Finally, the index WAS used (execution plan shows "clustured indexed
scan", 100%).

Step 11: Add ONE ROW into underlying table:
insert into maintable values ('test_add', 0)
results:
Table 'IView'. Scan count 2, logical reads 6, physical reads 0,
read-ahead reads 0.
Table 'MainTable'. Scan count 0, logical reads 2, physical reads 0,
read-ahead reads 0.

(1 row(s) affected)
time: 0 seconds.
Most probably, the aggregations had be recalculated on the view, but
because the clustered index exists, it did not take any time.
Execution plan shows a table insert actions (using clustered index),
then it shows sort (why table is reordered?), then finally it shows
"Clustered Index Update", rowcount=1 on IView.IX_IView_Status - the
index body itself is being updated.

So we see that on insert of the row into underlying table the index
body WAS updated.

Step 12: While 100,000 rows being inserted from one window, attempt to
insert one row into the table, from another window:
result: 31 seconds. (insert operation effectively blocking other
insert into table, making it to wait).

Step 13: While 100,000 rows being inserted from one window, attempt to
SELECT aggregates from , from another window (using as regular view):
SELECT Status, GrandTotal FROM IView
results: correct (500,000 rows), time: 37 seconds.
Insert operation effectively blocks ANY select attempt to the
underlying table.
Select has to wait for insert into table to finish (first 15 seconds),
then
for insert and rebuild of the index body to finish (another 15
seconds), since it is part of the transaction, then to build the
aggregation (7 seconds).

Step 14: while 100,000 rows being inserted from one window, attempt to
select using the indexed view:
SELECT Status, GrandTotal FROM IView  with (NOEXPAND)
results: correct (600,000 rows),
time: 31 seconds. (BOTH table body and index body are locked until
completion of all operations).

(however if we shall repeat:
SELECT Status, GrandTotal FROM IView  with (NOEXPAND)
the same results will be produced in 0 seconds, tables are not locked
anymore).

Step 15: while 100,000 rows being inserted from one window, attempt to
select count from  underlying table:
select count(*) from maintable
result: correct (700,000), time: 31 seconds.
The table itself is locked, untill the view index body is rebuilt!

Step 16: drop index on the view:
drop index IView.IX_IView_Status
while 100,000 rows being inserted from one window, attempt to select
count from  underlying table:
 select count(*) from maintable
result: correct (700,000 rows), time: 17 seconds.
Waits for 15 seconds for insert, then for 2 seconds for count result.
Index body does not exist any more.

Step 17: Indentical result will be for view:
SELECT Status, GrandTotal FROM IView
27 seconds (15 seconds to complete insert, 12 seconds for
aggregations).

Step 18: attempt to use indexed view is not possible:
SELECT Status, GrandTotal FROM IView  with (NOEXPAND)
compiler message:
Hint 'noexpand' on object 'IView' is invalid.
(indexed does not exist any more, indexed view can be used only as
regular view).

So thank you so much, the truth is reached.

Alexei

Quote:> So thank you so much, the truth is reached.

> Alexei

Glad I could help.

Gert-Jan

Some additional things that I found out about indexed views:

1) indexed view must contain a CLUSTERED index (i.e. without it the
mechanism will not work other that like with a regular view). Indexed
view may contain several non-clustered indexes (which will certainly
slow down the update process)

2) Regardless of whether clustered index is specified against one
column or against several columns (composite index), the indexed view
body invariably comprises all columns.

3) Indexed view can not contain text, ntext and image columns (even if
these columns are part of the clustered indexed defined).

For me the last point is an indication that all indexed view columns
are (internally) included  as part of the non-clustered index itself.
I.e. the indexed view and non-clustered index is basically the same
entity (i.e. the view is nothing but the clustered index and other
indexes, which in turn are just collection of pointers to the
clustered index rows).

just my 3 cents

Alexei

Alexei,

I suggest you buy the book "Inside SQL-Server 2000" by Kalen Delaney. It
explains all the topics you are trying to cover here. It will also
correct your views of the internal storage tables and indexes (No, the
branch level of the clustered index does not hold the non-indexed
columns. Only the leaf level of the clustered index does).

It will explain tables with clustered index, heaps (tables without
clustered index) and indexed views. It will also explain how indexes on
tables, heaps and indexed views are implemented. I am sure it will
answer most of your questions. It is worth the money...

Gert-Jan