performance of indexed views

I see their benefit -- trust me. one question I have is that it looks like my indexed view has to get updated each time the underlying base table changes. What happens if I do an insert or bcp into the underlying table -- will me table go offline while this data gets re-aggregated -- is there a way for me to schedule this? I see UDAs as well, and I think they are more flexible, but I'd be concerned that they are getting too far away from the optimizer and how are they refreshed -- recompile of the code?

Help..and I know it's marketing -- but what direction is the best for people that use the product, UDAs or Indexed Views.

Indexed views get updated in-line with table updates, just as if you basically had an additional index on the base table. So, if you perform an insert into the base table, it will not commit/finish until all indexes on the base table AND indexes from materialized views are updated. They basically are very similair in the way that the engine ensures additional indexes on the base table are kept updated. The same applies to how statistics are updated and managed, the same as the corresponding base table. An indexed view has an associated b-tree(s) structure backing it that must be kept up to date just as the base table is in real-time with data modifications.

As for Indexed Views vs. UDA's, they are 2 totally different technologies for different uses...I really don't see how the 2 would even compare. A UDA is calculated from an instruction and dataset you pass it at runtime, with no physical backing at all (unless you create for example an indexed computed column that is based on the UDA, but I won't go there). A UDA is basically the same as using any of the existing built-in aggregate functions in SQL Server (i.e. sum(), count(), min(), max(), etc.).

HTH

|||

The other thing to be aware of is that you always have to have a clustered index on an indexed view (this is what defines it) If you therefore have additional indexes on the indexed view, you will have the additional knock on impact when the underlying data is changed.

Indexed views are great in that they provide any easy way of providing the indexed view data quickly to the user with little effort, however you need to appreciate the impact on performance as well as the restrictions on how data is updated (SET statements etc).

|||I don't understand the 2nd part of the response you say they are different technologies, I agree, but they can be used for the same things no? If I wnatd to sum the sales of a bunch of regions and roll them up I could use an Indexed View for that -- aka summary table -- no? That's what I can do with a UDA? Help me here..|||Hi Chad -- I think there's something I just picked up on that I didn't relase -- that the Indexed View is a preset aggregate, while the UDA takes the data set and perform the aggregation dynamically, correct? If so, then is this just not a set of logic to run on the data set? Would you recommend a UDA over calling a SP to do the work? Do you see a lot of people using this, or would suggest it? What does it buy me -- just trying to understand how much CLR objects we want and should use based on the countless resources that say it's good for some stuff and not for others :)|||

Yes, that is correct. You could use either to achieve what you are attempting to get, however the biggest difference between the 2 technologies is that an indexed view is materialized on-disk, just like a table, and a UDA performs the aggregation on the data-set at time of request, like a query against a table.

A UDA is just like you mentioned, basically logic that is performed against the data set...the advantage of a UDA in 2005 is that you can create your own aggregates that don't already exist as pre-defined (i.e. sum(), min(), max())...for example, you could create a median() aggregate for example.

As for why you would use a UDA over a stored procedure would come down to a couple of things that would be different for many different scenarios, including performance, encapsulation, type of use, etc. For example, you could use a UDA just like you could use an existing pre-defined aggregate (i.e. within a select statement), like this:

select sum(column), myuda(column) from table

whereas you couldn't do the above with a stored procedure. Also, you may need to perform complex computational logic on the data, which the CLR would be better at. However, if you're just grabbing data, a stored procedure may be better...all would depend on the scenario.

HTH,