Posted by Doug on 02/06/06 07:05

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.

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.
Could I have "optimized" the view wiht hints? Nope. BTDT, didn't work.
The point is the engine is trying to kind of guess blindly how the data
really works, where a good analyst can KNOW how to get the data works.
Something to think about. Doug has written sprocs that broke a view
into pieces that made the whole thing significantly faster then a giant
select statement.
Btw, the compiling of a sproc or any sql statement is trivial in the
grand scheme of data access of large systems.

sql embedded in code is really bad. celko is just barely past that
stage, as he thinks sprocs are limited to replacing embedded sql, and
views are a better solution.
Not.

[Back to original message]