index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
The following link has some useful information on improving performance using indexed views:
http://msdn.microsoft.com/library/de...exedviews1.asp
However, to be specific to your scenario, you might consider running a workload through Index Tuning Wizard to see whether it suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:

> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA
|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
(Please reply only to the newsgroup)
|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.