Views on Views - recommendations please 
Author Message
 Views on Views - recommendations please
Are there any recommendations regarding the maximum number of views that can
be nested inside other views... i.e. I have a view that performs joins
across other views, those views have joins etc...

We have some views that are taking between 2 - 6 mins to compile and just
300ms to execute! Any ideas how we can speed them up?

We have tried using a stored proc but it makes little difference first time.

We run SQL 7 SP1 under NT Server, Dual PII 450Mhz, 512mb ram.



Mon, 24 Jun 2002 03:00:00 GMT
 Views on Views - recommendations please

Paul:

Treat views as you would any query: look at proper indexing, doing joins
intead of subqueries where possible (yet avoid too many joins), avoiding
SARGs that can't use indexes (like '<>'), using the readuncommitted
isolation level (or table hint) when appropriate, and all that other
performance-tuning stuff.

In addition, you might try using temporary tables to speed things up:
instead of having a view on top of a view, do an insert into a temporary
table and then base your view on that (all in the same batch). This could
speed things up and also avoid prolonged locking on underlying tables.

As a longer-term solution, if these views represent a large portion of your
business you might re-design your table structure so that nested views are
not necessary (denormalization, for example).

Hope this helps...

Kelland


Quote:
> Are there any recommendations regarding the maximum number of views that
can
> be nested inside other views... i.e. I have a view that performs joins
> across other views, those views have joins etc...

> We have some views that are taking between 2 - 6 mins to compile and just
> 300ms to execute! Any ideas how we can speed them up?

> We have tried using a stored proc but it makes little difference first
time.

> We run SQL 7 SP1 under NT Server, Dual PII 450Mhz, 512mb ram.



Mon, 24 Jun 2002 03:00:00 GMT
 Views on Views - recommendations please
MS warns against nesting views *too* deeply, so I generally try not to go
beyond 3-4 nested deep, and haven't had any problems with compile times....
Of course, the number of tables joined and the complexity of the views have
a lot to do with this as well..

--
Wayne Snyder
MCSE, MCT, SQL Server MVP
IKON Education Services -  C{*filter*}te, NC
(Please respond only to newsgroups.)



Mon, 24 Jun 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Please help to convert view on views to stored procedure

2. Please help to convert view on views to stored procedure

3. How are views on views on views executed ??

4. Views - Access project modifies the view syntax

5. Distinguishing user views and system views

6. To View Or Not TO View

7. create indexs in Views Based on Views

8. Viewing views

9. Calling SPs from views / views with parameters

10. SQL 7.0 views vs SQL 2000 views

11. VIEWs within VIEWs and Primary Key problem


 
Powered by phpBB® Forum Software