Re: Code in the database or middle tier (the CLR controversy)
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Jun 2005 14:51:07 -0700
Message-ID: <1117748941.505705_at_yasure>
>>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.
Date: Thu, 02 Jun 2005 14:51:07 -0700
Message-ID: <1117748941.505705_at_yasure>
[Quoted] Erland Sommarskog wrote:
> DA Morgan (damorgan_at_psoug.org) writes: >
>>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.
> > > My experience from reading posts on the SQL Server newsgroups, is > that most inexperienced users go for #2. And in the days of 6.5 > it was a good idea to rewrite that this into #6 for improved > performance. And while I may still give people this advice, it may > only be for esthetic reasons only, because I believe that the > optimizer now is smart enough to essentially rewrite #2 into #6 > internally. > > Which goes to show that what is the best is not always obvious. Of > course, #4 is likely to perform less effecient because of the distinct. > #3 and #5 are too convluted to make sense (and #5 won't rnn on > SQL Server.) #1 is potentially bad, because it could return a different > result. (You didn't include any DDL, so I don't know how the tables > are related.)
It is all available, including the data, at:
http://www.psoug.org
click on Morgan's Library
[Quoted] click on Explain Plan
#5 is a piece of work. It is the realization, in SQL, of a double negative. In Oracle #6 is definitely the superior solution with the specific data set I created. But I've never met a front-end programmer that knew enough SQL to write it.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Thu Jun 02 2005 - 23:51:07 CEST