View vs Join

  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    Hi all,

    I have a view with 2 inner joins and 4 left joins returning 25 Columns, 8 of which are calculated.

    My question is this.

    I always thought that views acted like tables, meaning that if I called this view and even though I'm only using 2 columns it doesn't affect the performance of the view.

    Therefor, should I still use the view for just returning 2 columns or should I extract the logic/query from the view and add it to my new query to get my results?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    HI All,

    Did some testing.

    Join on View:

    CPU time: 15Elapsed Time 27

    Logical Reads 453 (from on 6 tables)

    Join on Extracted Logic:

    CPU time: 16 Elapsed Time 312

    Logical Reads 457(from on 10 tables)

    The logical reads on the last one is confusing cause my query only has 7 tables in the join ?

    Is this once again because the view is already planned or what ever when it's created?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    Hi all,

    WEll the results above are not 100% as I had the query plan that skewed my numbers.

    Here is what I have now.

    With View:

    CPU Time:16

    Elapsed Time:16

    Logical Reads:451

    Scan Count:2

    Without View:

    CPU Time:16

    Elapsed Time:15

    Logical Reads:447

    Scan Count:3

    So I'm still not sure which is better?

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Michael Earl-395764

    SSC Guru

    Points: 53873

    A view is just a stored query. They are cached and perform the same as if you just ran the query itself unless you have schema-bound and indexed it.

    So, if you create a view and use it in a query or put the query the view is based on in a sub-query, they are going to perform the same. The optimizer will make sure of this. The big down side to using views is often they are re-used but not all of the joins or columns are needed. When you do this, you incur a bit of unnecessary overhead. Usually, if you re-create the logic you can get better results because you will not need to include all of the tables or columns in the view. On the other hand, re-creating logic repeatedly is bad because if you have to make a change, you have to make it in multiple places. So, seek balance.

  • Grant Fritchey

    SSC Guru

    Points: 396551

    Take a look at the execution plan. Usually (and there are exceptions) you'll see that a query with a view and a query without a view, both of which reference the same tables, in the same order, with the same join criteria, will generate very similar or identical plans.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    Thanks for all the advice everyone 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply