July 15, 2010 at 5:48 am
We have an application (developed for us) which relies heavily on nested views. So if the "main" (lets call it A) view looks at another view (B) where if B is opened it does a full table scan, does that mean when A is ran with a criteria it needs to let B do a full table scan first and produce all the results to hand to A ?
Also if the view has indexes does that make it any different ?
July 15, 2010 at 6:46 am
Paul Farnell (7/15/2010)
We have an application (developed for us) which relies heavily on nested views. So if the "main" (lets call it A) view looks at another view (B) where if B is opened it does a full table scan, does that mean when A is ran with a criteria it needs to let B do a full table scan first and produce all the results to hand to A ?Also if the view has indexes does that make it any different ?
Nested views are notorious for the problem you outlined. Yes, nesting the views simply compounds the complexity of the queries, putting a greater burden on the optimizer, and usually results in sub-optimal execution plans leading to horrificly bad performance.
The optimizer is extremely smart. It is possible for it to examine a view and identify that only chunks of it are used which will result in an execution plan that only accesses the necessary data. But, that depends on the optimizer having enough time to decode the view. When you start nesting views the optimizer simply runs out of time and goes with whatever plan it has, usually one that covers all tables referred to by all views being referenced since that's going to be the initial plan built by the optimizer.
As far as views on indexes goes... you can't really do that. A view is not a table. However, there is a thing called an materialized view or indexed view. Basically what that is, it replaces a view with a clustered index which does dramatically increase speed. However, there are a lot of caveats about the use of indexed views, how they can be created, and how well the data accessed within them is maintained. And one of the caveats is that it can't reference other views, only base tables. So your issue might preclude you using it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 15, 2010 at 7:28 am
Nested views should be used with moderation.
They are great when properly used - Ingrian encryption appliance relies on two layers of nested views and it works just fine.
On the other hand I will probably reject and send back to development team any view that does a full table scan on a large table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply