Are Views - good or bad!
-
While reading several of the responses today I came across one where the recommendation of using views was compared to the use of cursors. As opposed to hijacking that thread I thought I would ask the question here as my understanding of views just got blown out of the water.
I have in the past used views to help manage permission access , but more specifically to feed data to stored procedures that may be stored within multiple large tables, by reducing the number of fields presented and having the ability to read tables that may span several days information. i.e. in a data warehouse environment I may collect daily imported information into tables that only contain 10 mill rows and then have a new instance of that table collect more information-e.g. table1-01012007, table-02012007, table-03012007, etc. These tables may have 30 columns. Now I may have a view that UNIONS all the tables , but only uses 6 columns. The stored procedure would use the view to gather the information to be processed as opposed to the tables directly. - My question - is this a poor design usage of views , and if so what would be a better recommendation to collect the information from the tables for a multiple day requirement? i.e. 7 days would be approx 70mil rows
((oh btw - the views are generated dynamically using sysobjects to determine table requirements, so as new tables are added, so too is the view modified)
Now I have given a specific example - does anyone have thoughts on whether views are good or bad and for what specific reasons would they be used or dismissed?
** What you see, Depends on what you Thought, Before, You looked! **
-
Yes... I think you've done it the correct way for the right reason(s). And such "partioning" of data is a tried and true method (although I don't care much for date named tables and all they imply...)
The reason why many folks, including myself, have such a terrible time accepting views is because most people create views in a vain attempt to "reuse" code or code "simplification"... those ideas, by themselves, aren't such a bad ideas (kinda like a multi-row function on steroids), but the way some folks write views is a real performance killer and, as you said, can actually be many times worse than a cursor (still no excuse to use a cursor
) . And, if you get into Views-of-Views, a lot of times one huge (incorrectly written) view must fully materialize before the other view is successfully queried even when criteria are present in the calling sproc... drives memory and/or CPU usage right into the stops.So, like anything else having to do with SQL Server and programming in general... it depends... is it a good view, or is it a bad view? About 90% of the time, it's a bad view because it was written by a lazy developer without any code performance in mind.
I'd really like it if you'd provide the URL for the "other" thread you were talking about, Ross... wouldn't mind seeing what got you thinking about this...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I pretty much agree with Jeff's reply. Partitioning is definitely a valid technique using a view to pull it all back together. From a performance perspective the one thing I often see missed is adding a check constraint on each table so the optimizer can just work against one table instead of all (assuming the check constraint matches a portion of the where clause!). You might look at the partitioning features in 2005, which I find to be one of their more elegant solutions.
I do like views for encapsulating logic, but as noted views on views often perform badly, just the nature of things for now. The alternative is to often repro the logic in multiple places, giving good performance but making it harder to maintain.
-
Oh, bugger... I forgot about the check constraint thingy... Andy, I've temporarily lost my mind and my notes... can you review/provide a brief summary how the check constraints improves the performance of partioned views? If no one else needs it, I sure do
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I personally dislike views, except the partitioned and indexed types - however, the main reason is that when views are used within joins to other tables or other views the optimiser doesn't always exactly do what you might expect and you can get some very poor performance, mainly increased io due to scans.
I've just actually blogged about how to add a maxdop hint to a view to avoid parallelism, as I normally find with views they tend to create the query from hell.
I did some tests of views vs procs ( had an app which could not call procs so devs used views to join multi table data sets ) I tuned a specific query against a view, another problem with views is the fact the plans may be wrong for consecutive calls, but couldn't get better than around 6 seconds if I remember, I did the exact same query as a proc - just a few ms ( this was on 2000 so don't know if it's altered in 2005 )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I thought the check constraint was to ensure data integrity by ensuring modifications made through an updateable view would satisfy all conditions of the view definition. I need to get a refresher course on that area especially if it can help in performance and the optimizer plan.
Thanks for the feedback so far - I feel a little better on the way I currently use views
and my understanding of them.** What you see, Depends on what you Thought, Before, You looked! **
-
On the constraints, as a simple if not great example imagine that you have a really large customer table that you want to partition on last name. We'll create two tables, one that has last names beginning with A through M, the other N-Z. Then we create a view to union all them back together (UNION ALL is important, we dont want the UNION behavior that dedupes). If you run a query for lastname = x (leaving out indexes for now) you'll see that both tables are scanned. If you modify the tables with check constraints to match our A-M/N-Z partitioning, the same query will now only scan the appropriate table. SQL 2005 partitioning does the same thing with a partitioning function. Without the constraint you do get the ability to move the separate tables to different filegroups if you want which might improve performance, but the big gain is usually limiting the search. Distributed partitioned views are just another flavor of this, where you spread the data across multiple servers and create the same view on each server, using linked servers to pull it all together.
Note that in many cases you might choose to NOT union up everything, electing instead to archive the old data to a separate table that is excluded from most OLTP ops and can be brought deliberately into specific reporting/analysis queries.
-
Thanks, Andy...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
>>((oh btw - the views are generated dynamically using sysobjects to determine table requirements, so as new tables are added, so too is the view modified)
<<
Just remember to run sp_refreshview after the modifications
* Noel -
sp_refreshview - Thanks for that noeld, - I forgot about it - I know it would have come back to get me later on
Thanks Andy for the explination earlier.
** What you see, Depends on what you Thought, Before, You looked! **
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply