Posted by Serge Rielau on 06/02/05 17:12

DA Morgan wrote:
> William Stacey [MVP] wrote:
>
>> Not sure I see the issue here. Databases exist to service
>> applications (and hence a business need), not the other way around.
>> People could do selects from any .Net language/ADO.Net for years now.
>> Bringing the CLR into the db does not open up more wild
>> selects/updates/deletes as that goes. On the contrary, at least you
>> can store and manage that code in the DB and not have it spread out in
>> various client apps. Not sure it matters what current or future
>> language you use; perf issues will always have to be addressed from
>> all tiers - that is just part of the game. IMO, bringing the "bar
>> down" does not directly equate to soft code; I would, however, think
>> the reverse is true.
>
>
> You may not see an issue but my disagreement is 100%.
>
> The application front-end is irrelevant. I could use one front-end today
> and another tomorrow. No issue related to security, scalability,
> performance, integrity, auditability, etc. is tied to my front-end. If I
> change my front-end tomorrow ... no big deal ... but the data stored in
> the database is the value. If that gets changed I might as well lock the
> doors and go home.
>
> The other place where I take issue with you is what I read as an
> implicit assumption that a SQL statement is a SQL statement is a SQL
> statement: Which is clearly not true. Look at it from the standpoint of
> someone whose background is VB or C#. Which of the following SQL
> statement is the one to use? And yes they are all syntactically correct
> and all produce the exact same result set (in Oracle).
>
> 1.
> SELECT srvr_id
> FROM servers
> INTERSECT
> SELECT srvr_id
> FROM serv_inst;
>
> 2.
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT srvr_id
> FROM serv_inst);
>
> 3.
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT i.srvr_id
> FROM serv_inst i, servers s
> WHERE i.srvr_id = s.srvr_id);
>
> 4.
> SELECT DISTINCT s.srvr_id
> FROM servers s, serv_inst i
> WHERE s.srvr_id = i.srvr_id;
>
> 5.
> SELECT DISTINCT srvr_id
> FROM servers
> WHERE srvr_id NOT IN (
> SELECT srvr_id
> FROM servers
> MINUS
> SELECT srvr_id
> FROM serv_inst);
>
> 6.
> SELECT srvr_id
> FROM servers s
> WHERE EXISTS (
> SELECT srvr_id
> FROM serv_inst i
> WHERE s.srvr_id = i.srvr_id);
>
> My bet is you went straight for #4. And it is not the best
> by a very substantial margin.
1. You are not even using anything but SQL here. So all you prove is
that there may be some bad SQL there (surpise).

2. Don't draw conclusions from Oracle's optimizer to other DBMS...
The idea of SQL is that you say WHAT you want and the optimzier decides
how to best get it. DBMS may well give you surpisingly good plans.

I do not see the difference between a CLR/C/Java function/procedure and
a PL/SQL, SQL/PSM, T-SQL function/procedure.
All of which invite procedural logic.
Given that PL/SQL is also supported on the client IIRC it infact is in
the exact same position as CLR. Just because I know PL/SQL does not make
be an SQL expert.
I doubt the threshhold to learn PL/SQL is any higher than C# or VB.
I can bulk collect my result into an array and of I go...
The moment CURSORS and IF THEN ELSE enter the stage it's all shades of
grey...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Back to original message]