Index materialized views - improve performance?

Index materialized views - improve performance?

Post by Rohan Hattangd » Sat, 20 Sep 2003 20:00:06



Does anyone know how much performance is affected if views are materialized
for data that sees a lot of insert/update activity?

We have a client who is complaining of slow speeds and the main query that
is being used from the page where they are facing the problem contains joins
with multiple views that in turn have multiple joins in them. All this is
not really avoidable and I do not have the time to re-write the queries as
then they will have to go through a QA process and what not.

I have tuned the queries and created all the indexes that are appropriate
and performance is still slow. I feel that materializing the views will
help. But am hesistant to do so because I have a one-shot deal at this and
am worried about slowing down performance on the entire website and then I
will be in real trouble!

Has anyone out there done this sort of thing before? I know MS advises
against it but ......

Thanks,

Rohan

 
 
 

Index materialized views - improve performance?

Post by Andrew J. Kell » Sat, 20 Sep 2003 20:56:29


Rohan,

The materialized view (now called an Indexed view) is really for when you
are doing a lot of aggregates not just joins.  Do they have Enterprise
edition? If not then the Indexed view will not be seen automatically and you
will have to add hints for the queries to use it. If so then it shouldn't be
a big deal to add or remove it per say.  But if there are lots of updates it
may indeed slow down the inserts, updates and deletes.

--

Andrew J. Kelly
SQL Server MVP


Quote:> Does anyone know how much performance is affected if views are
materialized
> for data that sees a lot of insert/update activity?

> We have a client who is complaining of slow speeds and the main query that
> is being used from the page where they are facing the problem contains
joins
> with multiple views that in turn have multiple joins in them. All this is
> not really avoidable and I do not have the time to re-write the queries as
> then they will have to go through a QA process and what not.

> I have tuned the queries and created all the indexes that are appropriate
> and performance is still slow. I feel that materializing the views will
> help. But am hesistant to do so because I have a one-shot deal at this and
> am worried about slowing down performance on the entire website and then I
> will be in real trouble!

> Has anyone out there done this sort of thing before? I know MS advises
> against it but ......

> Thanks,

> Rohan


 
 
 

Index materialized views - improve performance?

Post by Rohan Hattangd » Sat, 20 Sep 2003 22:13:04


We do have Enterpise edition. I am still pondering over whether I should go
through with this or not.

Thank you helping.

Rohan



> Rohan,

> The materialized view (now called an Indexed view) is really for when you
> are doing a lot of aggregates not just joins.  Do they have Enterprise
> edition? If not then the Indexed view will not be seen automatically and
you
> will have to add hints for the queries to use it. If so then it shouldn't
be
> a big deal to add or remove it per say.  But if there are lots of updates
it
> may indeed slow down the inserts, updates and deletes.

> --

> Andrew J. Kelly
> SQL Server MVP



> > Does anyone know how much performance is affected if views are
> materialized
> > for data that sees a lot of insert/update activity?

> > We have a client who is complaining of slow speeds and the main query
that
> > is being used from the page where they are facing the problem contains
> joins
> > with multiple views that in turn have multiple joins in them. All this
is
> > not really avoidable and I do not have the time to re-write the queries
as
> > then they will have to go through a QA process and what not.

> > I have tuned the queries and created all the indexes that are
appropriate
> > and performance is still slow. I feel that materializing the views will
> > help. But am hesistant to do so because I have a one-shot deal at this
and
> > am worried about slowing down performance on the entire website and then
I
> > will be in real trouble!

> > Has anyone out there done this sort of thing before? I know MS advises
> > against it but ......

> > Thanks,

> > Rohan

 
 
 

Index materialized views - improve performance?

Post by Andrew J. Kell » Sun, 21 Sep 2003 02:18:31


The only real way to know is to test it.  The nice thing about the indexed
view is if it doesn't work then you can just remove it.  You should not have
to change any code.  Just monitor the system to set a baseline before and
compare it to after.

--

Andrew J. Kelly
SQL Server MVP


> We do have Enterpise edition. I am still pondering over whether I should
go
> through with this or not.

> Thank you helping.

> Rohan



> > Rohan,

> > The materialized view (now called an Indexed view) is really for when
you
> > are doing a lot of aggregates not just joins.  Do they have Enterprise
> > edition? If not then the Indexed view will not be seen automatically and
> you
> > will have to add hints for the queries to use it. If so then it
shouldn't
> be
> > a big deal to add or remove it per say.  But if there are lots of
updates
> it
> > may indeed slow down the inserts, updates and deletes.

> > --

> > Andrew J. Kelly
> > SQL Server MVP



> > > Does anyone know how much performance is affected if views are
> > materialized
> > > for data that sees a lot of insert/update activity?

> > > We have a client who is complaining of slow speeds and the main query
> that
> > > is being used from the page where they are facing the problem contains
> > joins
> > > with multiple views that in turn have multiple joins in them. All this
> is
> > > not really avoidable and I do not have the time to re-write the
queries
> as
> > > then they will have to go through a QA process and what not.

> > > I have tuned the queries and created all the indexes that are
> appropriate
> > > and performance is still slow. I feel that materializing the views
will
> > > help. But am hesistant to do so because I have a one-shot deal at this
> and
> > > am worried about slowing down performance on the entire website and
then
> I
> > > will be in real trouble!

> > > Has anyone out there done this sort of thing before? I know MS advises
> > > against it but ......

> > > Thanks,

> > > Rohan

 
 
 

1. Materialized Views performance

We are running 8.1.7 with 3 aggregate materialized views on a single fact
table. The views are created with query rewrite and on commit update. The
query rewrite works great and queries are very good. Update performance of
the base fact table is very poor with a 1000 row insertion taking 1.5 hours,
to do this in my own pl/sql code takes 2 seconds on the same machine.

Has anybody else seen this problem or similar and no the reason. We could
maintain the materialised views ourselves but getting Oracle to do it would
be the best option.

Many Thanks

Mike

2. SQL Server 6.5 Stops - Urgent

3. Index on the M_ROW$$ column (MATERIALIZE VIEW)

4. What data access methods Informix have ?

5. Indexes, Materialized Views and Data Marts.

6. Progressbar w/ updatebatch ADO, is this even possible?

7. Improving performance of view

8. Copy Data Base

9. How to improve view's performance?

10. how to improve table indexes for performance?

11. On Creating Indexes to Improve SQL Performance

12. improving performance thru indexing