SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View VS Select from Table


View VS Select from Table

Author
Message
VastSQL
VastSQL
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31697 Visits: 5828
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?
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3322 Visits: 531
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.

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)

Group: General Forum Members
Points: 129018 Visits: 18938
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

VastSQL
VastSQL
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31697 Visits: 5828
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (843K reputation)SSC Guru (843K reputation)SSC Guru (843K reputation)SSC Guru (843K reputation)SSC Guru (843K reputation)SSC Guru (843K reputation)SSC Guru (843K reputation)SSC Guru (843K reputation)

Group: General Forum Members
Points: 843960 Visits: 46625
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sue_H
Sue_H
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66460 Visits: 13821
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



Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3322 Visits: 531
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...ErmmErmm

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)

Group: General Forum Members
Points: 346702 Visits: 34110
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)

Group: General Forum Members
Points: 346702 Visits: 34110
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)SSC Guru (346K reputation)

Group: General Forum Members
Points: 346702 Visits: 34110
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search