Posted by Hugo Kornelis on 02/07/06 00:38

On 5 Feb 2006 21:05:10 -0800, Doug wrote:

>imagine if you will 5 tables. 3 of them are over a billion rows.
>
>you want to provide a primary key, and pull the detail data out of the
>children.
>
>For absolute, positive sure, in sql 2000, if you use a view, the engine
>will do this ugly huge join hash table thing on the entire thing, THEN
>look for your data.
(snip)

Hi Doug,

I'm sorry, but this is not correct. (Unless you are using indexed views,
but in that case the results of the view are pre-computed, so it
shouldn't take any noticeable time).

For non-indexed views, SQL Server 2000 will first replace the view-name
in the query with the view definition, then feed the result to the query
optimizer. The resulting plan will then be fed to the query optimizer,
which will try to find a good execution plan.

The resulting plan will be the same as when you had coded the complete
qeury from the base tables without using the views.

>Take the exact same code, use sense as to teh data lays out, and you
>can cut your response times from 20 seconds to sub second where it
>should be.
(snip)
> Doug has written sprocs that broke a view
>into pieces that made the whole thing significantly faster then a giant
>select statement.

Yes, there will certainly be situations where you can optimize a very
big and complex query by breaking it into pieces. The optimizer is
pretty good (IMO), but certainly not flawless. It lacks some techniques
that it realy should have, and if the complexity of a query increases,
it might choke on the explosion of the number of possibilities to
consider. The optimizer is also very dependant on good statistics.

On the other hand, there are also lots of queries that will perform lots
worse when you break them into pieces. I don't think that dismissing
views because you've had a bad experience with them is the right answer.
Especially since the bad experience is not really caused by the view,
but by the optimizer not doing a perfect job on a (probably) very
complicated query.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]