Are embedded views (Views within views...) evil and if so why?

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.

There are no problems with nesed VIEWs and in fact, you can do some
neat tricks using the WITH CHECK OPTION at various levels of the
nesting.

The real trick is to make sure that the VIEWs have sensible names and
meanings.

> There are no problems with nesed VIEWs and in fact, you can do some
> neat tricks using the WITH CHECK OPTION at various levels of the
> nesting.
>
So you have never tried to diagnose performance or bugs in code developed by
a third party using nested views then?

Nested views are a maintanence nightmare and should be avoided at all cost.

> The real trick is to make sure that the VIEWs have sensible names and
> meanings.

The real trick is to think through what you are trying to do and design your
schema properly in the first place.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1144051881.283954.296270@v46g2000cwv.googlegroups.com...
> There are no problems with nesed VIEWs and in fact, you can do some
> neat tricks using the WITH CHECK OPTION at various levels of the
> nesting.
>
> The real trick is to make sure that the VIEWs have sensible names and
> meanings.
>

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

I addition to Tony's post, I'd say that SQL Server's optimizer may be
confused by views. For instance, here is a situation I sometimes come
across:

select ... from some_view where
---- a very very very selective predicate on an indexed column
last_name like 'Zar%'

Unfortunately, the optimizer won't push the highly selective predicate
down the view's definition, so the query will run very slowly. However,
if you rewrite the query without using the view, the optimizer will
choose a better plan using the index on last_name.

One may hope that eventually SQL Server will ahve a better
implementation of views, but for the time being in a SQL Server
environment I would use views in moderation.

>> So you have never tried to diagnose performance or bugs in code developed
 by
a third party using nested views then? <<

Yes, I have.  The trick is to buidl VIEWs that look as if they are the
tables in an RDBMS  that was meant for one group of users, to control
them with DCL, WITH CHECK OPTION and INSTEAD OF triggers.
 

Perhaps the way you write them :)  What I saw was a well-constructed
sets of summary views set up for tricky accounting reporting rules.
Given the same set of account codes, the customer wanted to have VIEWs
for each country's laws about what was a deductable, taxable, etc.
(this year! - change teh those views next year)

Each VIEW could be changed to include a list of accounting codes that
would choke a moose if you had to have each and every programmer do it
by hand for each query.

The other advanrtage of VIEWs in other SQL products is that they will
be materialized and shared among sessions at run time, rather than
re-computed over and over.
 
schema properly in the first place. <<

I agree with that, but then we are getting back to avoiding IDENTITY
for relational keys and putting correctness over speed.

Tony, you still do not think of the problem as a whole at the
enterprise level.  You are working at the level of a single programmer
who sees only his narrow slice of the pie. When you stop being a grunt,
you will not make the same decisions.

>> Unfortunately, the optimizer won't push the highly selective predicate do
wn the view's definition, so the query will run very slowly. However, if you
 rewrite the query without using the view, the optimizer will choose a bette
r plan using the index on l
ast_name. <<

Ingres usually gets the in-line code expansion right.  DB2 is now just
about as good, with Oracle doing some things for sharing materialized
VIEWs among sessions.  When you have "end of the month" VIEWs, it makes
a big difference.
 

When you see what Oracle and DB2 can do with VIEWs because they are
aiming at the enterprise level and not the departmental level, it is
really impressive.

Hi Tony,

Thanks for your input.

Performance problems can be tricky to sort out with nested views.  For
example, I had a poorly performing query that was the product of
several "child" views.  It turned out that one of the child views about
3 levels deep had a ORDER BY clause that was slowing everything down.
When I removed the order by from that view, everything sped up.  It
took a while to diagnose, but it worked out OK.

Which brings me to a point here.  I develop these views and work very
hard to performance tune them and give them all logical and
object-oriented names.  Each view is named so that you can tell what
it's lineage is.  I write these views for a commercial application and
they are not modified in any way once they are shipped.  Once
everything is set up, they work great.

The biggest reason people seem to dislike embedded views is because of
the maintenance involved.  Being an application vendor, this is our
responsibility and our problem.  This is factored into the development
process, so it's not a real issue to me.  I guess the reason for
posting this thread is to learn more about the technical reasons - ie.
SQL Server reasons why these are bad, not necessarily the human
reasons, because I can handle these.

Thanks,

Rod.

> What I saw was a well- constructed
> sets of summary views set up for tricky accounting reporting rules.

From that I conclude that you've only come across one installation that has
used nested views and you've based your opinion on that!

To quote yourself - Why wasn't that business/display logic implemented in
the middle or client tier?

> Given the same set of account codes, the customer wanted to have VIEWs
> for each country's laws about what was a deductable, taxable, etc.
> (this year! - change teh those views next year)

You should not be building business logic into the database this way.

Even if you wanted to there are others methods aside from nesting views,
stored procedures gives a function block for one.

> I agree with that, but then we are getting back to avoiding IDENTITY
> for relational keys and putting correctness over speed.

The IDENTITY property used for SURROGATE KEY's or where a NATURAL KEY does
not exist has nothing at all to do with nested views.

By putting 'correctness' of your pure logical model over physical
implementation considerations will lead you into a poorly performing design.

When building a house you need to consider how you are going to implement
your plan, you don't use the same blue print for each piece of land.

>
> Tony, you still do not think of the problem as a whole at the
> enterprise level.  You are working at the level of a single programmer
> who sees only his narrow slice of the pie. When you stop being a grunt,
> you will not make the same decisions.

That is just laughable, its not me sat in a class room teaching kids! I'm
out there in the real world working for real clients with real business
problems.

You go theorise all you want, the class room bears no resemblance to what is
happening in the real world.

And as for enterprise, the majority of your solutions will not work in a
medium size system let alone one that needs to scale to the enterprise.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1144119337.940139.5880@i39g2000cwa.googlegroups.com... 
> a third party using nested views then? <<
>
> Yes, I have.  The trick is to buidl VIEWs that look as if they are the
> tables in an RDBMS  that was meant for one group of users, to control
> them with DCL, WITH CHECK OPTION and INSTEAD OF triggers.
> 
>
> Perhaps the way you write them :)  What I saw was a well-constructed
> sets of summary views set up for tricky accounting reporting rules.
> Given the same set of account codes, the customer wanted to have VIEWs
> for each country's laws about what was a deductable, taxable, etc.
> (this year! - change teh those views next year)
>
> Each VIEW could be changed to include a list of accounting codes that
> would choke a moose if you had to have each and every programmer do it
> by hand for each query.
>
> The other advanrtage of VIEWs in other SQL products is that they will
> be materialized and shared among sessions at run time, rather than
> re-computed over and over.
> 
> schema properly in the first place. <<
>
> I agree with that, but then we are getting back to avoiding IDENTITY
> for relational keys and putting correctness over speed.
>
> Tony, you still do not think of the problem as a whole at the
> enterprise level.  You are working at the level of a single programmer
> who sees only his narrow slice of the pie. When you stop being a grunt,
> you will not make the same decisions.
>

Hi Rod,

The majority of my problem is the maintanence of nested views in the
environment, not everyone is so careful in there use.

It must be noted that the overhead is also with development as well as
maintanence.

Anyway, technically; materialised views (indexed views) aside, you need to
make sure joins through the nesting are correct, you need to make sure any
use of UDF's are carefully considered. SQL Server simply expands the SQL for
the views into one big SQL statement (I wish we had access to this because
it would make problem diagnosis a little easier).

There is no performance advantage to using Views (unless you use indexed
views and there is a set of requirements for that which might not be
compatible with your application), they are simply a tool for encapsulating
schema logic.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<rod.weir@gmail.com> wrote in message
news:1144131741.030148.324550@e56g2000cwe.googlegroups.com...
> Hi Tony,
>
> Thanks for your input.
>
> Performance problems can be tricky to sort out with nested views.  For
> example, I had a poorly performing query that was the product of
> several "child" views.  It turned out that one of the child views about
> 3 levels deep had a ORDER BY clause that was slowing everything down.
> When I removed the order by from that view, everything sped up.  It
> took a while to diagnose, but it worked out OK.
>
> Which brings me to a point here.  I develop these views and work very
> hard to performance tune them and give them all logical and
> object-oriented names.  Each view is named so that you can tell what
> it's lineage is.  I write these views for a commercial application and
> they are not modified in any way once they are shipped.  Once
> everything is set up, they work great.
>
> The biggest reason people seem to dislike embedded views is because of
> the maintenance involved.  Being an application vendor, this is our
> responsibility and our problem.  This is factored into the development
> process, so it's not a real issue to me.  I guess the reason for
> posting this thread is to learn more about the technical reasons - ie.
> SQL Server reasons why these are bad, not necessarily the human
> reasons, because I can handle these.
>
> Thanks,
>
> Rod.
>