April 2, 2008 at 11:18 pm
Hi. Can you help me with this? - In terms of simultaneous users access, what's more efficient, a table or a view?
April 3, 2008 at 1:43 am
It depends from the users rights!
the more efficient is view sometimes when you have to use the particular data for some users, because you eliminate many unnecessary columns to view for the users for example if you have the table with 100 column and the users will see just 15 from them you should use the view! On the other hand when you create the view you can add the index in columns to make the view for more fast searching!
So if you have to retrieve the data from many tables it's sure that you will use the view to view these data properly!
The final, my opinion is to use the view!
Let see what's the others opinion!
Dugi
April 3, 2008 at 2:05 am
There is the results:
retrieving from the table with the same where clause it takes 1:33 (min:sec)
from view with 4 columns it takes 0:33 ( min:sec)
so the table definition is with 62 columns and over 1 500 000 records!
Dugi
April 3, 2008 at 6:49 am
Selecting from a table is different than selecting from a view and they shouldn't really be compared. Selecting from a view is basically selecting from a query that selects from a table (or tables). It's not any different than selecting from the table (or tables) directly using the same query.
Try it yourself. Type a select statement and time it. Then put that exact same select statement in a view and select from it. It should be basically identical. Look at the execution plans of both. They'll probably be identical too.
Views are meant to function as a mask, either hiding objects from users, limiting access for security reasons or storing complex joins that will be used over & over again in other queries. Except in the case of materialized views (different topic entirely) they are not the same as tables, which actually persist data, contain triggers, constraints, referential integrity, indexes and statistics.
"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
April 3, 2008 at 4:24 pm
I don't tend to make extensive use of views. Most of the time they are used to simplify query writing, or to provide an abstraction layer between users and the base table(s). In terms of efficiency, views can have adverse affects, but they are usually harmless.
In one system that I supported, views were used extensively and over time views were developed on top of other views which were in turn based on another view, etc... The worst example I found was 5 layers of views before you actually hit the base table! These didn't tend to be simple single-table views either, so there wound up being all kinds of unnecessary joins and gyrations going on which really killed performance. Now that's a pretty extreme example, but it really sprang from the idea that views simplify query writing. "The data is right there if we could just add this join to a new view..." Maybe I should add that this system used Oracle rather than SQL, but the result would be pretty similar in SQL.
I'm not against using views, but I generally prefer stored procs as an abstraction layer for data access.
On the other hand, indexed views can be a HUGE boost to performance when properly applied...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 13, 2008 at 2:31 am
In view we can give permission to those view to users, basically its for security purpose.
If users dont want to access some table, but they need some data from the table, here we go for views instead direct table access. Also applicable for insertions/updations/deletions.
Please try to access schema binding of the same to get more details on view Vs tbales.
April 13, 2008 at 9:45 am
Dangskie (4/2/2008)
Hi. Can you help me with this? - In terms of simultaneous users access, what's more efficient, a table or a view?
An indexed view will usually blow the doors off an aggregate of a table for SELECTs because the aggregation is "materialized". It does have a bad performance impact on INSERTs and UPDATES. Books Online recommends that you only use if for aggregates and queries that have a lot of joins and it has to follow certain other rules.
Other than that, as some have already said and as statistics are built, there is no difference between selecting from a view and an identical query when identical conditions are executed.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2008 at 7:48 am
On a side note, I personally despise views!! As a consultant they REALLY confound my efforts to examine/tune my client's queries!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2008 at 10:31 am
TheSQLGuru (4/14/2008)
On a side note, I personally despise views!! As a consultant they REALLY confound my efforts to examine/tune my client's queries!
Hear here! I second that... wasn't going to say it, but I've found that many folks don't know how to write an effective view especially if some form of aggragate is involved. Then, there's those good folks that do views-of-views with functions of functions... gotta love that. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2008 at 11:26 am
Well, to be honest, having to wade through a bunch of views/functions to find the source of a perf problem actually makes me MORE money since I bill by the hour. But it is just offensive to me to be so unproductive! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2008 at 1:10 pm
My first SQL database had 20 or 30 tables, 1 stored procedure, and 50 or 60 views. Some of the views went at least 5 levels deep before they ever hit a table. Many of them were built on the idea that I could include "all of these columns" in one place and then just use that view over and over and over again in my front end app.
It worked, but probably only because the largest tables had less than 1,000 rows.
Later, as I learned SQL a bit, that same database ended its life with 485 tables, 2 views, and over 3,000 procs. It also had 3 separate reporting databases based on it, on 3 servers, and a number of "automation databases" on separate servers, that also tied in to it. Plus two logging databases. All in all, six servers, 15 databases, all orbiting around that main database.
But, since I then had at least 1 clue as to what I was doing, it was faster than the original version, even when doing the exact same things in the user app. With hundreds of thousands of rows of data in many tables, and millions in a few of them. Much faster. Not even comparable.
Procs were definitely a major part of what sped all that up.
(In case anyone wants to know, what I call "automation databases" are databases that are never touched by a human except the DBA. All data work in them is SSIS/DTS, scheduled procs/scripts, etc. Data gets imported, updated, aggregated, cleaned, etc., there, then fed to OLTP and OLAP databases for actual use. I like to split them away from the human-oriented OLTP databases for performance and maintenance reasons. There's probably a better/more standard name for them, but I call them "automation databases" because that's their function.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 3:01 am
Hello All,
This is really a very informative topic abput tables & views.
I'm having a query abt the same.
Can we update the data of a table through view? but what if I'm selecting the data from 2 or more tables and not including all the fields of both the tables, in this case also will I be able to insert or update the data to the tables, using Insert into or update commands.
Guys Please give your views.
Rohit
April 16, 2008 at 7:50 am
On updating the underlying tables via a view, the answer is "it depends". Some views, yes, some views, no.
The rules on this are detailed in Books Online.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 3:19 pm
Why do you want to compare a table vs a view. They are different.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply