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

[rod.weir]

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.

[--CELKO--]

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.

[Tony Rogerson]

> 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--" <jc*******@earthlink.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.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.

[Tony Rogerson]

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

<ro******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.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.

[Alexander Kuznetsov]

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.

[--CELKO--]

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

Nested views are a maintanence nightmare and should be avoided at all cost. <<
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.
The real trick is to think through what you are trying to do and design your

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.

[--CELKO--]

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

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.

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

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.

[rod.weir]

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.

[Tony Rogerson]

> 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--" <jc*******@earthlink.net> wrote in message
news:11********************@i39g2000cwa.googlegrou ps.com...

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.
Nested views are a maintanence nightmare and should be avoided at all
cost. <<
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.
The real trick is to think through what you are trying to do and design
your

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.

[Tony Rogerson]

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

<ro******@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.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.

[Danny]

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

<ro******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.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.

[Erland Sommarskog]

Danny (dj*********@verizon.net) writes:

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.

Yes, that's one thing that make me nervous about views. Some thinks a
view has a column he needs that, and then he builds a new on that. Problem
is that when you expand the entire view, some underlying big table may
appear several times in view, being in fact joined to itself on the primary
key.

In our shop, we were for a long time without views at all. One project
added views into one corner of the database, where I think it actuall
made sense. (I was not involved myself.) I actually added two views to
the application for our latest version. These views are quite convoluted
and probably not very effective. But these views are there only for
compatibility. That is, I reworked some piece of data is stored, but
there were too many procedures to change right now, so these views
mimick the old tables

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 3 Apr 2006 19:55:38 -0700, --CELKO-- wrote:

(snip)

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.

Hi Joe,

Please, for the sake of who- or whatever you believe in, don't do this!

Put the list of accounting codes in a table and add a join to your view.
That's how proper SQL programmers do it.

--
Hugo Kornelis, SQL Server MVP

[--CELKO--]

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

I am a believer in the "Rule of Five" -- humans cannot easily process
more than five things.

Avoid transformations in the views <<
But this can be a handy way to assure that everyone converts English
units to Metric, etc. the same way. I picked that example because I
had a problem where one guy did his conversions to 2 decimal places,
another guy did it to 3, another guy to 4, etc. all the way out to 8
decimals and one usign FLOAT. The result was that sometimes we
destroyed inventory (okay, call it shrinkage, spoilage, or waste) but
sometimes we created inventory. Scary when you are dealing with food
products.
Create a view refresh process. Create a table driven procedure that refreshes the views in the correct order. <<

Amen.

[Doug]

What Alexander said.

Large views work for a bit for some customers. Eventually the
customer's needs will start to expand, the data will expand, and the
larger views will start to fade in satisfaction. Performance slows
down, your customers will need more detailed data, and in general the
views will reach their limits.
You can often buy yourself a little time by upgrading hardware on the
server and client, and buy yourself more time by moving towards stored
procedures.

In the bigger picture, your application is successful. It is storing
the needed data, and your customers are learning what is available, and
using that data to solve business needs.
The next step up the staircase is an OLAP solution. It is a fairly big
step, but it is very powerful, and will solve many of your power users
needs.

[Robert Klemme]

--CELKO-- wrote:

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.

Even SQL Server has some support for materialized views although they
call it "indexed view".

http://msdn.microsoft.com/library/de...es_06_6ptj.asp

Kind regards

robert