Post reply

Working with views

  • February 13, 2024 at 9:42 am

    #4377832

    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:

    1. Making a view from a table, making another view from another table. Then use a third view to join both
    2. Vs
    3. The third view taking the information directly from the source tables instead of from the 2 views build above

    Thanks a lot,

    Pedro

  • February 13, 2024 at 2:10 pm

    #4377955

    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.

     

  • February 13, 2024 at 2:11 pm

    #4377956

    Thanks a lot

  • February 14, 2024 at 1:42 pm

    #4378572

    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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 14, 2024 at 2:05 pm

    #4378608

    Just adding my 2cents;

    1. Are the views' queries fully covered? (look into covering indices)
    2. Can the cardinality from each view be limited up-front? (look into inline table value functions)
    3. 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