Re: Tuning

damorgan <damorgan_at_exesolutions.com> wrote in message news:<3DF8BD8B.1167F755_at_exesolutions.com>...
> "O.C." wrote:
>
> > Hi !
> > What is faster in embedded SQL (in PRO-C):
> > 1. One join of 5 large tables or
> > 2. Splitt the this big SQL into 3-4 parts (and use the output of one sql
> > result
> > as bind variables for the next sql statement)
> >
> > Indexes are in use.
> >
> > Thank you !
>
> One join of 5 tables is almost always going to be faster than splitting into
> separate statements.

Glad you put that "almost" in there, as I have a number of times fixed intractable problems by simply extracting out of multiple tables into flat files, used *ix tools to play about, then inserting into an un-normalized table from which to report. Of course, I won't even bother unless there is an order of magnitude increase in performance from an unacceptable baseline. And of course, CBO is, er, RULEd out due to, um, business requirements.

The downside is what it does for expectations about everything else!

>
> That said there are a huge number of variables related to the SQL, the data,
> the tables, the indexes, the optimizer, etc. that affect SQL statements.
>
> Rather than asking this question what you should do is run a test of two to
> five different possible solutions using EXPLAIN PLAN in SQL*Plus. And, of
> course, always use bind variables in any SQL.

Agreed.

>
> Dan Morgan

jg

--
@home is bogus.
Still get a kick out of http://www.despair.com , even if it is a bit
pricey.