I'm writing an on-the-fly report program that generates and executes
an SQL statement. The statement depends upon the choices users make
when selecting from several hundred columns spread across > 90 tables.
Since some of the data fields are optional and I won't be able to
match across tables, I need to use left outer joins to make sure I
don't drop rows. Unfortunately, execution time is somewhere around a
minute (there's other stuff going on, but the majority of the time is
in the execution). Way too long. For comparison, my queries run in
about 2 seconds when I don't have to do outer joins.
As a workaround, I'm doing something like this:
Select (list of all fields) from ... where... join all Select (list of all fields except the ones from tables that I
can't
match) from... where key not in (select key from optional table)
This runs in the 5-10 second range, but it's going to be a nightmare
to code, especially the "where" clause.
I never know which columns the users will pick so I can't really
hardcode the queries. Everything is generated on the fly. I've been
asked not to index any columns. Any ideas about how I can make this
thing run faster so I can drop the workaround, or any alternative
ideas? I think we're using version 7.3.2.
Thanks a lot for your help.
Lorraine
