Creating an Index on a View 1

[awaria]

SQl Server 2000 Enterprise:

I have a very poor running view that is a union of a WORK table to it's coorresponding HISTORY table.

Here is the view, if an index can be used, can you detail out the syntax for adding an index to this view. I read that views could be used, but not if your query includes a UNION staement, and this one does. Is that true. If so, what can you recommend to help the performance of this view.

Thank you,

Andrew
______________________
CREATE view aa_GLTrxLineItem as
select a.dex_row_id,a.jrnentry,a.openyear,a.refrence,a.trxdate,(a.debitamt - a.crdtamnt) as TrxAmt,b.actnumst,b.actnumbr_1,b.actnumbr_2
from gl20000 a
inner join Gl00105 b
on a.actindx = b.actindx

union all

select hist.dex_row_id,hist.jrnentry,hist.hstyear,hist.refrence,hist.trxdate,(hist.debitamt - hist.crdtamnt) as TrxAmt,b.actnumst,b.actnumbr_1,b.actnumbr_2
from gl30000 hist
inner join Gl00105 b
on hist.actindx = b.actindx
__________________________________

 

[markros]

In your tables gl2000, gl00105, etc. do you have indexes on ActIndx fields?

That's the only thing that can speed up the retrieval from this view.

Are you using select * from View or you're trying to add filter to this view or join with some other tables?

PluralSight Learning Library

 

[awaria]

Markos, thanks for the post.

I apologize for my basic questions, I'm not expereinced with indexes.

So, you don not create an index on a view, but, on the tables that the view is referencing?

I noticed that the tables have several indexes.
Each table has a clustered index and several non clustered
indexes.

What is the strategy for using the index types?

Thanks again,

Andrew

 

[awaria]

I forgot to mention that the existing clustered indexes are not on the field ACTINDX.

ACTINDX is referenced in most of the non clustered indexes, in various field positions.

Andrew

 

[markros]

I suggest to first review the execution plan for the view. It may already show missing indexes. Then you may also run it through Database Tunning Advisor.

PluralSight Learning Library