Re: The IDS, the EDS and the DBMS
Date: 6 Sep 2004 13:56:50 -0700
Message-ID: <e9d83568.0409061256.5c59df3f_at_posting.google.com>
"Laconic2" <laconic2_at_comcast.net> wrote in message news:<taydnXNN5coe8KHcRVn-gg_at_comcast.com>...
>
> A while ago, when I was in a more fun loving mood, I started a thread
> called "stupid database tricks".
>
> One of the stupid database tricks I mentioned was:
>
> "We don't use views at this shop. we tried them a while ago, and found
> that they yield bad performance."
I think one of the most outstanding features of SQL-DBMS'es (and naturally RDBMS'es) is the concept of view merging.
For example, if we have the view
CREATE VIEW RED_PARTS
(P#,PNAME,COLOR,WEIGHT,CITY)
AS
SELECT P#,PNAME,COLOR,WEIGHT,CITY
FROM P
WHERE COLOR = 'Red'
the query
SELECT *
FROM RED_PARTS
WHERE CITY = 'London'
will automatically be converted into
SELECT *
FROM RED_PARTS
WHERE CITY = 'London' and
COLOR = 'Red'
The other possibility for the DBMS
would be to first materialise the view (i.e.
produce an internal temp table with
red parts) and then pick the 'London'
rows.
It requires an intimate knowledge of
the underlying DBMS to understand which
strategy it uses in which cases. This
changes with DBMS product and version.
Needles to say, view materialisation
will in most cases kill performance, so
it is no wonder that one sometimes hears
statements such as the one above.
Dunno much about algebra, but from what
I have understood, a more orthogonal,
RM-obiding language than SQL would give
the implementors an easier go at optimizing
views (and other relational expressions).
This view merging feature is something
that is next to impossible in a procedural context.
Consider :
PROC: MYPROC
CALL GET_NEXT_RED_PART
DO WHILE (...)
IF part.city = 'London' THEN
PRINT part
CALL GET_NEXT_RED_PART
END END MYROC What is the algorithm that knows how to
insert the city-condition into
GET_NEXT_RED_PART??? Best Regards,
Lauri Pietarinen Received on Mon Sep 06 2004 - 22:56:50 CEST