|
|
Posted by Danny on 04/04/06 15:04
Rod,
I agree with Tony on this. In general multiple layers of views are a
maintenance nightmare. I got a 1TB reporting DB in this situation. It
nests views about 5 layers deep. The system has had this design for nearly
4 years now and has gotten out of hand. In the beginning it's easy. Years
down the road and thousands of changes it's mind boggling. And yes we use
Crystal on top of it running hundreds of reports per day. If you are
willing to swallow that pill here a couple of lessons learned.
1. Keep all or nearly all the logic at one level. Using joins at multiple
level can produce very poor plans.
2. Avoid transformations in the views. Performance can really suffer by
using things like Case statements. You may think.. Just this one report
will use this view and it doesn't need to be fast. However, a year from now
I promise someone will see the name of the view and what it returns and pile
more code on top of it.
3. Create thin indexes on columns in join conditions. Look closely at the
execution plans against the views. Notice that in most cases joins are
still processed even if no columns from the joined table is used in the
select.
4. Create a view refresh process. Create a table driven procedure that
refreshes the views in the correct order.
Good luck on this dark path....
Danny
<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]
|