|
|
Posted by JRStern on 06/04/05 03:00
Don't know what this has to do with CLR controversy, but ...
If indeed those six alternatives are equivalent, then #4 is certainly
the best one to give any competent RDBMS. Except that you might try
learning ANSI join syntax.
You've got to be joking that #5 would produce a better execution plan
on any real database, unless you have so few rows that the whole thing
is moot, and you forgot to define any appropriate keys or indexes.
Are you telling us that you saw better *peformance* between one plan
and another, or are you just giving us your (mis-)interpretation of
what the plans look like? Certainly on SQLServer it's easy to misread
the plans in regards to what kind of execution you will actually get.
And of course, the issue is moot for the additional reason that Erland
observed, that one would expect, and he showed for SQLServer, that the
optimizer will mostly ignore these syntactic variations and come up
with the same plan in most or all cases, anyway.
Josh
On Thu, 02 Jun 2005 14:51:07 -0700, DA Morgan <damorgan@psoug.org>
wrote:
>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.
Navigation:
[Reply to this message]
|