Posted by Tony Rogerson on 04/04/06 11:09

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

[Back to original message]