• 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