Hi Hugo,

In my experience an indexed view with aggregates may introduce very
serious lock contention if used in OLTP environment. Although there are
some ways around (I can elaborate if necessary), I'd prefer to use
index covering if an OLTP table VeryLargeOrders is simultaneously
modified by different connections.

What do you think?

On 19 Jan 2006 14:10:51 -0800, Alexander Kuznetsov wrote:

Hi Alexander,

It's a trade-off. Indexed views introduce some overhead on data
modification. Not what you want on a high-speed transaction-processing
system. (But you don't want long-running aggregating queries on such a
system either - you'll either use a seperate reporting DB or use dirty
reads and accept not-entirely-accurate reports)

But if an application has lower amounts of transactions to digest and
much aggregated reporting to do, indexed views are a no-brainer.

Hi Hugo,

thanks for answering. From where I sit, the overhead on data
modification does not look much more serious that the overhead incurred
by one additional index. But if you create an indexed view with
aggregation, such as

select sum(amount) sumAmount, count_big(*) cnt from sales

you effectively serialize all the modifications against sales table.
Although sometimes there are workarounds, the problem is quite serious.

What do you think?

On 24 Jan 2006 14:07:36 -0800, Alexander Kuznetsov wrote:

Hi Alexander,

I don't know the internal processes used to keep an indexed view current
on modifications of the base table.

In the example you mention, I can easily imagine that the effect would
be serializing the modifications, since the indexed view will have one
row only which has to be locked for each modificfation to the base
table. In a more realistic example that includes a GROUP BY clause in
the indexed view, I expect (and hope) the internal processes to reflect
modifications in the view to take row locks, not table locks. So that
would not serialize the modifications, unless they affect the same
group.

But I never tested this, so I might be wrong. In the end, it all boils
down to the advise that is very common in this group: test it on your
end, in your DB and with your data - for that's the only reliable way to
find out how it will work in your case!

can't insert into ...because of nulls problme!
Return random number from trigger
Help on "splitting up" data in a field
Can You SQL Inject this code?
Unicode encoding problem ?