Working with views
-
Dears,
Hope this message finds you well
I would like to ask if there is any difference from a performance perspective between joining to make a View from two base tables, or if its better to create from each base table a like to like view and after, create the view which is a join of both viewes
like:
- Making a view from a table, making another view from another table. Then use a third view to join both
- Vs
- The third view taking the information directly from the source tables instead of from the 2 views build above
Thanks a lot,
Pedro
-
In general, it's better to avoid nesting views -- so for your case, make the view you want from the two base tables.
Why?
- Reduces ambiguity for those that need to maintain/examine the view.
- Reduces the risk that SQL Server will not be able to generate a good execution plan.
-
Thanks a lot
-
Just remember, a view is nothing but a query (materialized views are a different discussion). As a query, is it better to write a specific query for a specific need, or, is it better to combine two or more generic queries, making for a very large query indeed? Simple answer is that a specific query is better.
Views are a handy way of obscuring structures or providing masking for data or other stuff like that. They are 100% NOT meant to be objects like in code and therefore used as building blocks for other queries. This type of anti-pattern causes all sorts of headaches.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Just adding my 2cents;
- Are the views' queries fully covered? (look into covering indices)
- Can the cardinality from each view be limited up-front? (look into inline table value functions)
- Are the views a part of semantic abstraction/layer? (a different architecture and design discussion)
One could easily write a book on the subject, luckily, Grant already has so check out his excellent work!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply