|
|
Posted by DA Morgan on 06/03/05 00:51
Erland Sommarskog wrote:
> DA Morgan (damorgan@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
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@x.washington.edu
(replace x with u to respond)
Navigation:
[Reply to this message]
|