Path: utzoo!attcan!uunet!stephsf!wengland
From: wengland@stephsf.stephsf.com (Bill England)
Newsgroups: comp.databases
Subject: Re: Join Contest
Message-ID: <181@stephsf.stephsf.com>
Date: 16 Jul 90 00:25:02 GMT
References: <5265@plains.UUCP>
Reply-To: wengland@stephsf.COM (Bill England)
Organization: Stephen Software Systems, Inc., Tacoma WA
Lines: 36

In article <5265@plains.UUCP> gus@plains.NoDak.edu writes:
>
>
>Recently I heard that an IBM-type db person claimed that it's
>not uncommon in commercial db applications to join as many as
>10-15 (maybe even 30) tables in a single query.
>
>This seems like an incredible number of joins, especially if the
>tables are large, on the order of 1/2 million tuples.
>
>Any comment/references, etc.?

  In a project for this spring we regularly created selects that would 
join 5 to 10 tables.  The rational being that the database engine can 
gather all of the required data faster than building several selects and 
asking the engine to do them seperately.  Many of the joins were outer 
joins, collecting data that may or may not exist for a primary record.

    If you have data from several tables to display or report, it is almost 
always more efficient to join the tables and issue a single select. Rather
than look up data with several selects inside of a loop.

   The largest problem I have found with joining many tables is that it
becomes less intuitive what your result will be as the number of tables 
joined increases.    < This is, perhaps, an understatment.  :-) >

PS.  The database system is Informix.4gl/compiled running on a Sequent.

 +--------
 |  Bill England
 |  Stephen Software Systems, Inc.,   Tacoma Wa.
 |  wengland@stephsf.com              +1 206 564 2122
 |
  * *      H -> He +24Mev
 * * * ... Oooo, we're having so much fun making itty bitty suns *
  * *