 |
|
SQL Server
Tips by Burleson |
The Small Problem with View
Merge
A sophisticated DBMS performs these additional steps after or during
the view merge:
All three of our DBMSs are sophisticated.
But here's an example of a problematic view and query:
CREATE TABLE Table1 (column1 INT PRIMARY KEY, column2 INT)
CREATE TABLE Table2 (column1 INT REFERENCES Table1, column2 INT)
CREATE VIEW View1 AS
SELECT Table1.column1 AS column1, Table2.column2 AS column2
FROM Table1, Table2
WHERE Table2.column1 = Table1.column1
SELECT DISTINCT column1 FROM View1 <-- this is slow
SELECT DISTINCT column1 FROM Table2 <-- this is fast
— Source: SQL Performance Tuning, page 209.
The selection from the view will return precisely the same result as
the selection from the table, but Trudy Pelzer and I tested the
example on seven different DBMSs (for our book SQL Performance
Tuning, see the References), and in every case the
selection-from-the-table was faster. This indicates that the
optimizer isn't always ready for the inefficient queries that the
Query Rewriter can produce.
Ultimately, the small problem is that the "view merge" is a
mechanical simpleton that can produce code that humans would
immediately see as silly. But the view-merge process itself is so
simple that it should be almost instantaneous. (I say "almost"
because there are lookups to be done in the system catalog.)
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
