Single huge view or mutiple smaller views

Question

Single huge view or mutiple smaller views

clementstore

SSC Enthusiast

Points: 140
More actions
September 9, 2010 at 1:50 am

#146949

Hello there,
I wonder if there is any performance difference between querying a huge single view or multiple broken down views? ( both totalling same number rows ) As I usually notice that only 50% of CPU usuage will be in use by SQL server on a query. If views are spread in multiple, then it take multiple query to retrive data from multiple views. Would this overcome any resource constraint if there is any?
I m using SQL server 2005 SP3 and windows 2003 standard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Toby White SSCertifiable Points: 6187 More actions · Answer 1

The performance is based on what is happening with the base tables. Whether the views are wide or narrow isn't as important as what operations the query plan will perform on the base tables when using the views. If you can provide more background or specific information I might be able to give you a more indepth answer. I am not sure what a broken down view is - a view with fewer columns?

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

I agree with Tony. SQL Server breaks down the query in views and attempts to build a tree just as if you had embedded the view code in the FROM clause of your query.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Is this a theoretical question or do you have to deal with a real-world scenario?

If the latter, you could test the effect by using two scenarios and compare the execution plan as well as statistics.

Based on the query design you might get identical execution plans. Or not. 😉 It depends.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

I tend to use "Divide'n'Conquer' methods. Unfortunately, those methods rarely include views... just queries within stored procedures.

I also tend to avoid views with aggregates because people do some terrible things like trying to filter on the aggregated columns using WHERE clauses. When it takes an hour to return the top 10 from a view, you know you've really done something wrong.

--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)

clementstore SSC Enthusiast Points: 140 More actions · Answer 5

Thanks ppl .

Views generated from both approaches are from same set of tables ( over 100 ) and

they are not partitioned. They are generated by some stored procedures on monthly update of master data. These tables will be queried by reporting server (business objects) with requests on views containing about 20 tables each time. If i have a single view, i can manage the access right more easily ( filterd at row level by WHERE statement in reporting server ). If in 6 views, I would be managing 6 stored procedure and 6 set of access rights.

Cheers. Definitely will try out the above suggestions.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

clementstore (9/10/2010)
Thanks ppl .
Views generated from both approaches are from same set of tables ( over 100 ) and
they are not partitioned. They are generated by some stored procedures on monthly update of master data. These tables will be queried by reporting server (business objects) with requests on views containing about 20 tables each time. If i have a single view, i can manage the access right more easily ( filterd at row level by WHERE statement in reporting server ). If in 6 views, I would be managing 6 stored procedure and 6 set of access rights.
Cheers. Definitely will try out the above suggestions.

The code that builds the views will easily manage the permissions because the code should be using simple ALTER commands to modify the views. The views sound like they're simple "partitioned views" and having multiple partitioned views is a time honored way of managing data for performance. Having one BIG view for such a thing may not be the way to go because partitioned views don't always work out the way folks want them to.

--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)