Re: Code in the database or middle tier (the CLR controversy)

Serge Rielau wrote:

>> 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

My point was exactly that there can be bad SQL. Demo 5 took me 15 minutes to write because it is sometimes hard to write intentionally bad SQL (far easier to do it by accident).

This thread is going to all major c.d. groups so the Oracle example was as good as any ... but the point is that even after running through the optimizer ... the result set is the same but the query plan and the cost are vastly different.

The difference is not the language. Bad T-SQL is bad T-SQL just as bad PL/SQL is bad PL/SQL. The difference is that if one is encouraged to use the database language one is likely to have the code written by those with an actual understanding of databases. Let any self-taught homeschooled  self-annointed programmer write in the database and the chances for bad SQL go up by more than a magnitude.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)