Posted by Serge Rielau on 02/04/06 23:32

Tony Rogerson wrote:
>>One does not exclude the other. You can use procedures as external
>>interface for the application and still use views within the procedures as
>>appropriate.
>
>
> Absolutely, so long as you are using stored procedures as the external
> access mechanism then that is a good thing.
OK.. perhaps we violently agree? You are hung up on embedded SQL. I'm
not debating that point at all.
>
> I hope you are not suggesting you embed SQL queries into the application?
No I'm not. Each their paradigm. I'm just trying to differentiate
between two fundamentally different SQL objects.

<snip>

> These two, asside from an unmeasurable amount of CPU time in plan
> compilation will give the same performance...
So the CALL is free? No doubt it's highly optimized, but it won't be free.
>
> SELECT ... FROM yourview
>
> Or..
>
> CREATE PROC yourproc
> AS
> BEGIN
> SELECT .... FROM yourview
>
> END
You are looking only at one end of the picture.
Let's focus on the scenario where the select is NOT in the application,
but it is inside of a nested proecdure.

Let's use the following SQL (never mind if I mess up the dialect..
that's not the point):
CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, C1 INT)
CREATE TABLE C(pk INT NOT NULL PRIMARY KEY,
fk INT FOREIGN KEY T(pk), C2 INT)
CREATE PROCEDURE nestedproc(IN arg)
BEGIN
SELECT c1, c2 FROM P, C WHERE P.pk = C.fk AND P.pk = arg;
END

CREATE VIEW nestedview AS SELECT c1, c2 FROM P, C WHERE P.pk = C.fk;

Now let's consider the following:
"What is the maximum value c2 for a given arg?"
Using the procedure you CALL the procedure with the argument and you
then have to process the resultset (with swoem sort of logic unless I
missed some T-SQL feature) to find the MAX.
Using the view I simply write:
SELECT MAX(c2) FROM nestedview
WHERE pk = arg

What's the difference? An SQL optimizer can clearly see that there is
nothing interesting in P. It will DROP access to table P altogether
because first it projects out c1, then it kicks out P using the foreign
key relationship to prove that the join is row preserving.
Even better, with the right index (fk, c2 desc) this whole thing will be
done in a single I/O.

Using the procedure you have broken this vital link between the consumer
of the data and the data access. The DBMS has no clue that c1 will never
be consumed because the procedure is written generically for users who
want to retrieve c1 and c2.
Now, even if your DBMS were not to do this optimization today. Chances
are it will next time you upgrade; the DBMS will show why you paid more
for the new release than the old one.

The problem lies NOT in the application. The problem lies in the fact
that procedures are being nested and important context is being lost.

> The smart developer considers such things as scalability, maintainability
> and supportability, they do not try to apply 80's programming models where
> most products didn't have the concept of stored procedures, I remember - I
> used to program PL/1, CICS and DB2 on the mainframe that way.
You are older than me... anyway being too heavy on procedures is poison
for scalability. When I teach customers I teach that both logic and SQL
have their place. What I call "trivial procedures" are my prime example
to explain what I mean.

Cheers
Serge

PS: Check out table functions (aka parameterized views).
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Back to original message]