Re: BIG Joins
Date: 26 Jul 1994 18:10:09 -0400
Message-ID: <3141k1$c8g_at_search01.news.aol.com>
In article <775161862snz_at_aboy.demon.co.uk>, MarkB_at_aboy.demon.co.uk (M a r k) writes:
>
> > I`m curious... how many people out there know of a runtime environment
> > where a "common" operation on a database involves a join of more than,
> > say, 5 tables?
> >
> > It seems to me this is fairly rare, limited mostly to large reports
> > that would get spooled off at EO[DWMY].
>
>> Depends what you mean by 'runtime environment'.
>>I've written forms triggers using eight or nine tables, with maybe >> a couple of look-ups thrown in. These came back in under ten seconds, >> mainly due to a cunning hub-and-spoke database design. >>I've just finished a query for a Financials10 report which necessitated >>a twenty-five table join (in order to ORDER BY fields in different >>subledgers). I wouldn't say it was greased lightning, but you don't>>have to run it overnight. (Actually, we've yet to prove that this is >>true in a high-subscriber business environment.)
>> REST OF MESSAGE DELETED Great big SQLs can be fun but depending on the tool in which they are embedded SQLs can be performance limiting. Even if the program does not need to be "run overnight" it probably can be improved by simplifying the SQL. For instance in PRO*C it is likely that joins requiring a high number of tables, complex decodes, and nested joins can be accomplished a lot faster using the C language rather than Oracle's brain. I have seen improvement of 1000x and faster by simply letting C do the work instead of ORACLE. Sure it is easier to let SQL do the work (and more fun/challenging) but that is rarely the purpose of the program. If you insist on great big complex SQLs take them out of your programs/forms and let the tool do it for you. You can still challenge yourself by creating the SQLs for testing the tool's ability to do what the poorer performing SQLs could have done.
Please e-mail any thoughts on this to bharter_at_aol.com.
Boyd Received on Wed Jul 27 1994 - 00:10:09 CEST