Re: SQL Tuning question

DA Morgan wrote:
> Dan wrote:
> > Another question. I'm trying to find out the most efficient way to
> > join multiple tables. Take a situation in which you are joining
> > multiple tables on the same field. Is it better to join table1 to
> > table2 then table2 to table3 like below:
> >
> > select TABLE1.partno from TABLE1, TABLE2, TABLE3
> > where table1.partno = table2.partno and
> > table2.partno = table3.partno;
> >
> > Or is it better to join all from the table1 field, like below:
> > select TABLE1.partno from TABLE1, TABLE2, TABLE3
> > where table1.partno = table2.partno and
> > table1.partno = table3.partno;
> >
> > I did a quick explain plan and scenario #2 showed a lower cost but I
> > don't know if that's because of my data or not. I saw a book example
> > which used scenario #1. Can I make a hard and fast rule on this type
> > of thing, or does it depend on the data, the driving table, etc?
> >
> > Thanks,
> > Dan
>
> I doubt there is a generic answer to the question.
> --
> Daniel A. Morgan

I think you are right. The cardinal rulle I follow for optimizing queries is: KNOW THY DATA!

what works for three tables of say invoices data might perform miserably for three tables of bill of materials data. So there is no "hard and fast rule" to follow.

Dan,
The guideline is

make the query functional first and optimize later as needed.

  HTH,
   ed Received on Fri Oct 21 2005 - 11:22:28 CDT