Posted by --CELKO-- on 06/28/07 20:32

>> It's not illegal. It's non-standard, but in SQL Server (check the name of this group!!), it's legal and documented. And in many cases much faster than the ANSI-standard equivalent - an omission I keep kicking the optimizer team for until they finally get it right, but until that day we developers have to code our way around it. <<

Okay, would accept "proprietary and in total violation of the
programming model in ANSI/ISO Standard SQL and RDBMS concepts" instead
of "illegal"?

>> Oh boy, I really hope that Richard runs a quick test or two (oh wait, one should be enough with such glaring errors) before deploying this alternative" in production. <<

I am not happy with the idea that one value is being replicated among
multiple tables in the same schema. I have the feeling that we should
not be doing this UPDATE at all. But I have no DDL, so the best I can
do is translate from dialect to Standard directly.

>> I think that this snippet of code is a good contender for not only the worst code ever posted to Usenet, but also for the worst formatting. <<

Hey, I am not even close the worst code ever posted to Usenet!! You
ought to see the stuff people send me in emails :) I just finished
one "quickie Email consult" for an old friend where we removed 70-75%
of the lines of code and reduced the time from 98 minutes to 4 minutes
for time-to-completion for a complex report using CASE expressions
instead of sub-queries. It took three emails and ~15 minutes of my
time. If I could re-do the schema design, I could get it down to 1
minute or less.

What I was not expecting in that exercise was that replacing INNER
JOIN operators with the WHERE clause syntax improved performance in
DB2. I have no idea why, but perhaps their optimizer has a bias in
ordering the tables. The infixed JOIN operators are required to
behave as if they are executed in left-to-right order, while the old
"FROM.. WHERE.." syntax is not so required.

[Back to original message]