Re: Need to query 4 tables....3 are 'subordinates'...(need outer join?)

> > 3. I'm working w/ some mainframe programmers who say they never write
> > crazy SQL. Instead, they just go out and get the data for each row
> > one table at a time. Meaning, for each fully-populated row of data
> > they wished to gather for final presentation/writing/whatever, they
> > would actually make four queries - one to each of the four tables -
> > resulting in a lot of queries. My Main table's 15 M rows would have
> > our Java app making a total of 60 M queries (4 * 15 M) - nice. That
> > mainframe must have some serious gitty-up. Is there something about
> > the load capabilities of mainframe databases I don't know about?

It's not clear whether your mainframe programmers are in fact getting the best possible performance, by fetching
rows from only one table. If indeed what they are doing is a relational join, it's very possible that the algorithm they are using is exactly the same as the algorithm that would have been chosen by the optimizer inside the RDBMS package, if they had specified a single query with several tables.

If that's the case, then they probably would have been better off to let the RDBMS do it. The performance they would get depends on the DBMS product, and the version of it. It also depends on the design of the database, including indexes.

What SQL is judged "crazy" and what SQL is judged "sane" is sometimes in the eye of the beholder. People who don't understand relational operations like join, and people who are forced to program against a database that was perversely designed, often view complex SQL as "crazy" when it's not. People who think they can do a better job than the optimizer are sometimes kidding themselves, sometimes not. Received on Fri Oct 04 2002 - 17:54:12 CEST