|
|
Posted by Tony Rogerson on 04/03/06 19:11
Hi Rod,
Please don't use nested views! I think in point (3) you have mentioned what
I'm refering too, its a maintanence nightmare.
Consider somebody like me coming in and needing to work through a
performance problem, it takes significantly longer to work out problems with
queries using nested views because it takes an extra step to work out what
each view is doing and how the optimiser is expanding the views to formulate
the real query plan.
Single level views are fine so long as they are only used within the
database or a reporting application - always try and use stored procedures
when using an application with fixed access paths.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<rod.weir@gmail.com> wrote in message
news:1144030997.998657.136740@u72g2000cwu.googlegroups.com...
> Fellow database developers,
>
> I would like to draw on your experience with views. I have a database
> that includes many views. Sometimes, views contains other views, and
> those views in turn may contain views. In fact, I have some views in
> my database that are a product of nested views of up to 6 levels deep!
>
> The reason we did this was.
>
> 1. Object-oriented in nature. Makes it easy to work with them.
> 2. Changing an underlying view (adding new fields, removing etc),
> automatically the higher up views inherit this new information. This
> make maintenance very easy.
> 3. These nested views are only ever used for the reporting side of our
> application, not for the day-to-day database use by the application.
> We use Crystal Reports and Crystal is smart enough (can't believe I
> just said that about Crystal) to only pull back the fields that are
> being accessed by the report. In other words, Crystal will issue a
>
> Select field1, field2, field3 from ReportingView Where .... even
> though "ReportingView" contains a long list of fields.
>
> Problems I can see.
>
> 1. Parent views generally use "Select * From childview". This means
> that we have to execute a "sp_refreshview" command against all views
> whenever child views are altered.
> 2. Parent views return a lot of information that isn't necessarily
> used.
> 3. Makes it harder to track down exactly where the information is
> coming from. You have to drill right through to the child view to see
> the raw table joins etc.
>
> Does anyone have any comments on this database design? I would love to
> hear your opinions and tales from the trenches.
>
> Best regards,
>
> Rod.
>
Navigation:
[Reply to this message]
|