Improving performance on a view

[SkyhiKeeper]

I have a view which consists of 4 tabme with 2 outer joins on these tables, however this view takes a very long time to open the main parent table has 1.6 million records in it, the view takes 10 to 15 mins to open, any suggestion that I can use to improve the performace on the view opening would be much appreciated

 

[hmckillop]

Is the table a frequently changed table. You could try and use indexed views, which should permit quicker access to selective data within the view.
But doing outer joins in a view arent always great especially on table sizes you have.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

Depending on what you are doing with this view, you might get better perfomance from:
indexing the view (only available if you have Enterprise Edition) consider creating a covering index that coversall all the join fields and any fields inthe order by or where clauses.)
Using a Stored procedure instead of a view
Do you really need all 1.6 million records? Why? If not a where clause to limit the records should help.
Are you doing calculations inthe view? Perhaps it might be better to precalculate some of these and store the information.

For more specific advice, you need to post the code for the view. Also please descibe how you are using the view. If you care writing SQL against this view to add the limits dynamically (since a view won't take parameters, be aware that the view first pulls all the records, then the sql limits them. It is a better practice to create separate stored procedures that act directly on the tables in this case.

Questions about posting. See faq183-874