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

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

-- 
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp