--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> Hi John,
> what is the reason for using LEFT or RIGHT join to that many tables?

It's pretty typical for a largish business application. Let's say you
are generating a report and have an invoice to print.
You would need to pull in all the fields from the invoice, join the
customer table to get the customer name, join an address table to get
the customer's primary address, join an inventory record to get the
item description and price, join a taxes table to get the taxes
applied, etc etc etc.

> Cannot any of them be INNER joined? Like Sean (who is a heavy

No, that doesn't apply in a hiearchical structure where you may not
have a record to accompany. I.E. to use my example if a client has no
address or primary contacts or phone numbers entered then there will
be no record. If you use an inner join you will not get the invoice
at all simply because the user didn't enter an address record for the
client. Using left and right joins is mandatory defensive programming
in a real world application.

That example is extremely simplified, my actual query is a hiearchical
work order object that contains many items each with many sub items
who in turn have taxes, users etc etc.

> I have never tried
> joining (anywhere near) that many tables, but I think it would be
> worth checking your query before concluding that the reason is too
> many tables (35 doesn't sound that much, even though LEFT and RIGHT
> joining is more complex than simply using JOIN). So, can you show us
> the query and the generated plan?

It's over 13k of SQL, you probably wouldn't want to see the whole
thing here!

I've been writing business software for many years and it's not
unusual at all. Particularly when it comes to generating reports that
aggregate so much data from so many places. The difference is that we
decided to support both Firebird and MS-SQL in our latest application
so I'm doing parallel development, primarily developing the query on
MS-SQL because the tools are so much easier to use and integrate into
my Visual Studio IDE, then running them through a de-MS-SQL-IZER and
testing them against FireBird. So far so good, it's just these limits
I'm starting to run into that MS-SQL doesn't have that are getting a
bit worrying.

Anyway it seems I was pulling in too much data for Firebird so I've
since broken it out by pre-fetching some of the data into business
object collections that I can inject into the final business object
collection before sending it off to the report. (we don't report
directly off the query but off a business object collection in c#)

That seems to have resolved the problem (at least until the customers
get their hands on it and find all the weaknesses that I couldn't!)

Cheers!