View VS Select from Table

  • Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

  • VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    Obviously, why?  I'd say it won't make the slightest difference.  But what I say isn't important - give it a try both ways and see what happens.

    John

  • I stand with John as the data fetching method of SQL will be same for both. May be I am wrong ,waiting for more experts to comment.

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    I have a very hard time believing that a view improved performance unless it was an Indexed View.  A normal view is just an encapsulation of a query as if you wrote the query in a FROM clause or in a CTE.  Can you site any article or post any demonstrable code that proves that a normal view offers any type of a performance improvement?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    I'd be interesting in know how - maybe it's not that obvious.
    It was my understanding that the optimizer doesn't even know it's a view - all that gets to the optimizer is the query that is the definition of the view. So it wouldn't be the optimizer slowing things down. What exactly makes it slower when either the query or the view and it's executing the same thing?

    Sue

  • Sue_H - Wednesday, February 7, 2018 6:45 AM

    subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    I'd be interesting in know how - maybe it's not that obvious.
    It was my understanding that the optimizer doesn't even know it's a view - all that gets to the optimizer is the query that is the definition of the view. So it wouldn't be the optimizer slowing things down. What exactly makes it slower when either the query or the view and it's executing the same thing?

    Sue

    Hi Jeff & Sue,
    Normally In MS SQL Server, query plans are getting stored in execution plan cache for all SQL queries, i.e either normal query or view etc., These queries will get dropped from the cache if they are unused for a long time. Because the unused space will get occupied by new queries and for executions.

    So obviously a view or a sql query will be the same for execution plan cache. I was going to reply the OP to suggest an Indexed view to run the queries faster than a normal table queries.

    Regarding views, Yes I too need a real time example to demonstrate whether view / sql queries will be faster...:ermm::ermm:

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    Uhm.... no. Not in any way "obviously."

    A view is just a query. It's not optimized in any way beyond or differently than any other query. I've no idea where the idea that views are either better, or worse, than other queries comes from. It's just wrong. In fact, what happens more often than not is that people treat a view as if it was a table, it's not, and start joining views to views and nesting views inside of each other. Then, performance suffers. Not because you used a view, but because you've forced the optimizer to unpack your view and join it to another view that it had to unpack that included other views that were unpacked... at which point the optimizer gives up, tosses you a functional, but not optimized plan, and reports a timeout.

    Views aren't magic. They're just queries. They certainly aren't methods of performance enhancement. Nothing in a view would make it work better than an identically structured query. The exception to that is a materialized view, but that's no longer simply a query. It's a different storage object entirely.

    We really do need to be cautious about giving advice out like this.

    "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

  • Sue_H - Wednesday, February 7, 2018 6:45 AM

    I'd be interesting in know how - maybe it's not that obvious.
    It was my understanding that the optimizer doesn't even know it's a view - all that gets to the optimizer is the query that is the definition of the view. So it wouldn't be the optimizer slowing things down. What exactly makes it slower when either the query or the view and it's executing the same thing?

    Sue

    No, the optimizer knows it's a view. However, it treats a view like what it is, a query. So while the optimizer understands that it's working with an object, the definition of that object is such that it gets treated as if it wasn't anything special (because, except for materialized views, it isn't).

    However, don't take this as criticism. You're dead on accurate. It's not the view that's making something slower or faster. It's something about the query against the view vs. the other query and what the optimizer can do with it that is defining performance here.

    "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

  • VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    No.

    A view is nothing but a mask that makes a query look like a table. It doesn't turn it into a table. It just appears to be one when writing a query. As you can see in the other posts I've made, the optimizer knows that it's dealing with a query and treats it appropriately. Here's an introductory blog post I wrote on this topic.

    To improve performance, your best bet is to write each query as a distinct unit, returning only the columns you need when you need them. This means that you don't get the ease of code reuse (write a view one time, use it for 20 different queries), but you will get improved performance (each distinct query gets it's own execution plan, with minimal time spent on simplification and other processes necessary when dealing with views). Execution plans are also your friend here in understanding why a query is behaving in the way it is.

    "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

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 6:57 AM

    Sue_H - Wednesday, February 7, 2018 6:45 AM

    subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    I'd be interesting in know how - maybe it's not that obvious.
    It was my understanding that the optimizer doesn't even know it's a view - all that gets to the optimizer is the query that is the definition of the view. So it wouldn't be the optimizer slowing things down. What exactly makes it slower when either the query or the view and it's executing the same thing?

    Sue

    Hi Jeff & Sue,
    Normally In MS SQL Server, query plans are getting stored in execution plan cache for all SQL queries, i.e either normal query or view etc., These queries will get dropped from the cache if they are unused for a long time. Because the unused space will get occupied by new queries and for executions.

    So obviously a view or a sql query will be the same for execution plan cache. I was going to reply the OP to suggest an Indexed view to run the queries faster than a normal table queries.

    Regarding views, Yes I too need a real time example to demonstrate whether view / sql queries will be faster...:ermm::ermm:

    The way it is worded, the poster asked if it is faster to use a view or the query. Your response was:
    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    So when you say yes it's obviously faster, it makes no sense at all. It has nothing to do with execution plans. I may not know much but I do understand the cache size is limited and not everything ever executed will be in cache, that plans age out and such.
    I wanted to know why you are saying it's "obviously" faster with a view because it isn't.

    Sue

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 6:57 AM

    Sue_H - Wednesday, February 7, 2018 6:45 AM

    subramaniam.chandrasekar - Wednesday, February 7, 2018 12:53 AM

    VastSQL - Wednesday, February 7, 2018 12:48 AM

    Experts,

    We have a procedure which select from a lot of tables. Will there be any performance improvement if we create a view for that select query instead of fetching directly from Table?

    Obviously yes, We'd implemented the same when we wanted to share our data to shared services team.

    I'd be interesting in know how - maybe it's not that obvious.
    It was my understanding that the optimizer doesn't even know it's a view - all that gets to the optimizer is the query that is the definition of the view. So it wouldn't be the optimizer slowing things down. What exactly makes it slower when either the query or the view and it's executing the same thing?

    Sue

    Hi Jeff & Sue,
    Normally In MS SQL Server, query plans are getting stored in execution plan cache for all SQL queries, i.e either normal query or view etc., These queries will get dropped from the cache if they are unused for a long time. Because the unused space will get occupied by new queries and for executions.

    So obviously a view or a sql query will be the same for execution plan cache. I was going to reply the OP to suggest an Indexed view to run the queries faster than a normal table queries.

    Regarding views, Yes I too need a real time example to demonstrate whether view / sql queries will be faster...:ermm::ermm:

    You posted "Obviously Yes" and gave a totally anecdotal example of where it supposedly helped you.  If that's not what you meant, then what you posted is extremely misleading, especially to someone that may not know better.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 6:57 AM

    Regarding views, Yes I too need a real time example to demonstrate whether view / sql queries will be faster...:ermm::ermm:

    But you said: "Obviously yes"
    Now you're saying that's not true?
    Again, back to what I said, we must be very cautious when offering advice, especially if we're not quite ready to back it up.

    "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

  • Grant Fritchey - Wednesday, February 7, 2018 7:40 AM

    subramaniam.chandrasekar - Wednesday, February 7, 2018 6:57 AM

    Regarding views, Yes I too need a real time example to demonstrate whether view / sql queries will be faster...:ermm::ermm:

    But you said: "Obviously yes"
    Now you're saying that's not true?
    Again, back to what I said, we must be very cautious when offering advice, especially if we're not quite ready to back it up.

    Hi Experts,

    Apologies for shorter / anecdotal reply for my earlier post.

    Personally I prefer views for faster executions. Again table and view are not meant be compared because their purposes are different.  

    I'd wanted to suggest an indexed view rather than a normal view for faster performance.

    Regarding view and table topic, here was an another,

    https://www.sqlservercentral.com/Forums/Topic478962-360-1.aspx

    I don't want to debate here but the suggestion from my end would be views, because views are pretty much useful in several ways in daily works.

    Again going back to the topic, Selecting from table or view for records would be same in most of all scenarios, because the query plans are stored in

    execution plan cache for all SQL queries, i.e either normal query or view etc.,   

    I'd related this post with our project work where we'd implemented a view in place of table to deliver the work to external clients. If you have any more

    examples or any suggestions with you on these, Could you please share with me /  us ? I'd like to note down your answers / suggestions.

  • subramaniam.chandrasekar - Wednesday, February 7, 2018 10:10 PM

    Grant Fritchey - Wednesday, February 7, 2018 7:40 AM

    subramaniam.chandrasekar - Wednesday, February 7, 2018 6:57 AM

    Regarding views, Yes I too need a real time example to demonstrate whether view / sql queries will be faster...:ermm::ermm:

    But you said: "Obviously yes"
    Now you're saying that's not true?
    Again, back to what I said, we must be very cautious when offering advice, especially if we're not quite ready to back it up.

    Hi Experts,

    Apologies for shorter / anecdotal reply for my earlier post.

    Personally I prefer views for faster executions. Again table and view are not meant be compared because their purposes are different.  

    I'd wanted to suggest an indexed view rather than a normal view for faster performance.

    Regarding view and table topic, here was an another,

    https://www.sqlservercentral.com/Forums/Topic478962-360-1.aspx

    I don't want to debate here but the suggestion from my end would be views, because views are pretty much useful in several ways in daily works.

    Again going back to the topic, Selecting from table or view for records would be same in most of all scenarios, because the query plans are stored in

    execution plan cache for all SQL queries, i.e either normal query or view etc.,   

    I'd related this post with our project work where we'd implemented a view in place of table to deliver the work to external clients. If you have any more

    examples or any suggestions with you on these, Could you please share with me /  us ? I'd like to note down your answers / suggestions.

    You're doing it again.  There are two types of views.... regular views and "materialized views" (indexed views in SQL Server).  You can't just make the blanket statement of "Personally I prefer views for faster executions" because regular views do NOT make for faster executions.  Indexed/materialized views?  Sure, they can save the day for performance but not regular views.  Since you're the one making the claim that views make for faster performance, you need to provide some code or a link to someone's code that proves that REGULAR views provide a performance advantage.

    To be sure, you'll get no argument from us that Indexed/Materialized views DO, in fact, provide performance advantages, sometimes extremely so, and that's NOT what we're looking for repeatable, demonstrable code for.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

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