• 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