Re: Maximun number of unions in a single query

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 08 Jun 2005 10:05:21 -0700
Message-ID: <1118250337.193348@yasure>

fitzjarrell_at_cox.net wrote:
>
> hpuxrac wrote:
>

>>Oh, that is easy. It is directly related to the idiot factor of the
>>writer of the SQL.
>>
>>--
>>Billy
>>
>>Too much caffeine or are you off your medication?
>>
>>If you don't want to answer a question don't answer it.
>>
>>Mr. Fitzpatrick at least gave a reasonable answer first before joining
>>in your weak response.

>
>
> Why would someone use a UNION? To 'join' data from tables which cannot
> otherwise be joined. Asking for the LIMIT on the number of UNIONs one
> can use certanly indicates the schema was not designed properly, and
> will produce a performance nightmare in production. Somewhere along
> the design/implementation line someone with little clue contributed
> much, else why would it be necessary to string along innumerable UNION
> operators to generate a result? In a well-thought-out system I can see
> no reason to do so. That statement does NOT indicate there may not be
> a valid reason at some point for a one-off query to use UNION, but to
> implement an endless list of select ... UNION select ... UNION select
> ... is ridiculous, which is what the original poster is apparently
> attempting to do. Why would the question of how MANY are allowed in a
> list have surfaced?
>
> Stringing UNIONs in a never-ending list is foolhardy, and, as I stated
> before, belies a poor database/schema design. The OP would be better
> served, in my opinion, to work on redesigning the schema rather than
> creating queries which will be performance bottlenecks.
>
>
> David Fitzjarrell

I agree with everything you've said but you should keep in mind that there are a lot of commercial software apps being used by companies that have horrible designs and thus require this kind of horror story. One that comes to mind immediately is name Hanson and is used by a very large city in Washington State to manage permitting. The city is wasting tens of millions of dollars to implement just such a beast.

Others like Maximo, another horror story, also come to mind.

Maybe we should put together a web site that DBAs can log onto and vote for the worst software applications based on design and quality of SQL. Then other DBAs could use it as ammunition to fight management's seemingly pathologic desire to purchase these blivets.

If others are interested in joining in ... my votes for worst apps are: Siebel
Hanson
Maximo
Baan
SAP
(not in any particular order)

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Wed Jun 08 2005 - 12:05:21 CDT