July 17, 2002 at 6:25 pm
Folks,
I work on a team that has to make collective DBA decisions about our table structures, etc.
One issue came up that took me by surprise. A key part of a solution to a particularly thorny issue I was trying to solve, was to use a standard View. By using a view, I could relate three tables together by their linking foreign keys, and thereby keep the final coding (VBscript/ASP) simpler.
A co-worker insisted that in her experience, Views are only for gathering data for end-user display. They aren't to be used for "real" work.
I've never heard of this before. But she's a bright lady and may be onto something.
Is there any rule of thumb about when NOT to use a view to simplify what would otherwise be complicated queries?
Thanks,
- Tom
kelleher-[at]-tkelleher.com
July 17, 2002 at 8:32 pm
The key to views are their reusability and the simplification of maajor complex queries. However if a view does not represent a query that is reusuable for more than a few situations repeatedly then they may still be usefull or may not be worth the storage (altough space is not at a high premium anymore). Also keep in mind you can create updatable views which can make working with data easy (see BOL for more details).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 07/17/2002 8:33:44 PM
July 17, 2002 at 9:23 pm
re: "The key to views are their reusability and the simplification of maajor complex queries. However if a view does not represent a query that is reusuable for more than a few situations repeatedly then they may still be usefull or may not be worth the storage (altough space is not at a high premium anymore)."
If I can hazard a summary of your answer then (with good-natured tongue-in-cheek :), you're saying:
Views are appropriate for simplifying complex operations when used a lot (but not if not), but sometimes even if not, though storage can be an issue, except it isn't.
Whew! 😉
I guess I'm still in the same place, though. But that may be my own fault, for posing a vague question. I'm clear that this view is a good solution -- it eliminates an aggravating sub-query from scores of other frequently used queries, and provides additional data fields that none of the base tables have (by comparing values and returning True/False's for instance). So it seems to meet the criteria you mentioned.
My foremost question is taken from her concern: Is there any validity to the notion that views are not for doing "real" work? Put another way, is there any reason that a well-made view cannot be used as if it were just another bona-fide table? (The underlying tables are all on the same server as the view, so there are no additional distribution complications.)
Another question: Does a view give worse performance than the raw query that underlies it?
And you mentioned storage. This is new to me. Do views use an inordinate amount of storage for their data?
I am looking for ANY kind of validation of her point, that views are somehow not meant for this kind of usage. It seems like a bit of a prejudice in a way, that she has against them, but she's a smart person and I don't want to wave her off.
Thanks again,
- Tom
July 17, 2002 at 10:47 pm
As long as it's not an indexed view then the storage used is very small, just what it takes to describe the view.
I believe that views can and should be used for "real" work. I agree with Antares that they should be used to simplify and clarify complex queries. Views are also an excellent tool for hiding parts of tables that some users dont need to see, a far simpler approach than column level security.
Hard to give you a definition of when I use a view. One would be the complex query, sometimes it's just the clarity. Recently I created a view that unioned three tables, two of which were in the db and one was in another db. Not that the query was difficult, but having the view made it transparent to query and easy to work with.
Andy
July 18, 2002 at 7:15 am
First let me thank Andy for a bit of clarity on somethings. I appologize that is not my strong point. Now for
quote:
Another question: Does a view give worse performance than the raw query that underlies it?
The answer is no. It will be comparable in every way as the underlying query even when the execution planned is displayed. (I think in 2000, but I do forget that the query is compiled when stored in a view which gives it a slight and even undetectable advantage).
Views are nothing more that the query underneath it. I use views often in some of my databases and not at all in others. It really comes down to, like I said, the ability to reuse or the simplification of a task use periodically that may be touch to remember.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 7:33 am
Antares & Andy,
Thanks for the input on this. It pretty well confirms my own guesswork, but you guys are tapping into years of experience that I haven't had, and that helps.
And Antares, thanks for allowing me that moment of poking fun. It wasn't lost on me that you are one of the gurus of this site, and as a newcomer I didn't want to offend.
- Tom
July 18, 2002 at 10:13 am
No offense, I am a lowsy writer and I know it. In fact I usually have my wife read over important stuff first before I email or put out.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 11:25 am
We're a pretty good natured crowd. No one minds a little fun here as long as we dont stray too far off track (you did not) - one of our strengths so far is there is very little noise in this discussions!
Antares - I don't see anything wrong with your writing. I think its often that by the time several of us read and comment on each others notes that the person asking the question gets a more well rounded answer than perhaps any one of us could provide!
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply