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

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 04 Jun 2005 18:40:26 -0700
Message-ID: <1117935507.945926_at_yasure>


[Quoted] Erland Sommarskog wrote:

> In this example: not very much. But digest this:
>
> FROM notes nte, insloannotes iln, contnotes con, instruments ins,
> trades trd, accounts acc, customers cst, addresses adr,
> customeraddresses cad, insloanstartnotes ils, countries cou,
> notprintedcontnotes npc
> WHERE nte.nteid = npc.conid
> AND npc.printdate IS NULL
> AND nte.nteid = iln.conid
> AND iln.loanconid = con.conid
> AND nte.trdid = trd.trdid
> AND con.insid = ins.insid
> AND iln.loanevent = 'I'
> AND iln.loanconid = ils.conid
> AND nte.accno = acc.accno
> AND acc.cstno = cad.cstno
> AND acc.cstno = cst.cstno
> AND cad.isdefaultsendout= 1
> AND cad.adrid = adr.adrid
> AND adr.coucode = cou.coucode
> AND trd.butcode = 'IIL'
> AND (trd.tradedate BETWEEN _at_busdatefrom AND @busdateto OR
> (_at_busdatefrom IS NULL AND @busdateto IS NULL))
> AND (con.accno = _at_accno OR @accno IS NULL)
>
> versus:
>
> FROM notprintedcontnotes npc
> JOIN notes nte ON nte.nteid = npc.conid
> JOIN trades trd ON trd.trdid = nte.trdid
> JOIN insloannotes iln ON nte.nteid = iln.conid
> JOIN contnotes con ON con.conid = iln.loanconid
> JOIN insloanstartnotes ils ON con.conid = ils.conid
> AND iln.loanconid = ils.conid
> JOIN instruments ins ON con.insid = ins.insid
> AND trd.insid = ins.insid
> JOIN accounts acc ON nte.accno = acc.accno
> AND con.accno = acc.accno
> JOIN customers cst ON acc.cstno = cst.cstno
> JOIN customeraddresses cad ON acc.cstno = cad.cstno
> AND cad.isdefaultsendout = 1
> JOIN addresses adr ON adr.adrid = cad.adrid
> LEFT JOIN countries cou ON adr.coucode = cou.coucode
> WHERE npc.printdate IS NULL
> AND iln.loanevent = 'I'
> AND trd.butcode = 'IIL'
> AND (trd.tradedate BETWEEN _at_busdatefrom AND @busdateto OR
> (_at_busdatefrom IS NULL AND @busdateto IS NULL))
> AND (nte.accno = _at_accno OR @accno IS NULL)
>
> I know which one I rather have for breakfast.

And I'd likely agree. But then I wouldn't use this query as a demo of EXPLAIN PLAN for what should be equally obvious reasons.

> Actually, as long as we are into inner joins, both syntaxes are
> ANSI-compatible. It is when it comes to outer joins it matter.
> Here both SQL Server and Oracle have their own propritary operators.

[Quoted] [Quoted] Not true. Oracle has for multiple versions now allowed either ANSI or ISO syntax.
> --
> 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

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sun Jun 05 2005 - 03:40:26 CEST

Original text of this message