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

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@xxxxxxxxx> wrote in message
news:1144131741.030148.324550@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Does FTS in SQL 7 have known issues with not indexing records?
    ... You get 0 rows could not be indexed typically for the below reasons. ... the account SQL Server runs under is not registered with MSSearch. ... > I've just looked back at the full population I ran yesterday, and have> noticed that it also logged a warning event. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem when changing Views
    ... There aree several reasons. ... I use neither of the tools, but I guess that Design View resubmits the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Running multiple instances in production environment. Good or bad?
    ... > Wayne Snyder, MCDBA, SQL Server MVP ... > that login sysadmin role membership... ... >> to be negative to running multiple instances in a production ... > There are several reasons why most of us would TRY very hard NOT to have ...
    (microsoft.public.sqlserver.server)
  • RE: BCP.EXE fails in 90 where it succeeded in 80
    ... I'll be careful to do so in the future, ... v8.0 (exact same SQL 2005 server, exact same BCP command line)? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: Matthew Parris
    ... together a list of unanswered questions ... you were asked that several times, I recall. ... Careful what you wish for. ... The reasons for you failing ...
    (uk.rec.cycling)