Re: Query performance: DivideAndConquer vs. BruteForce

_Generally_ (YMMV) when > 3 tables are joined in series (T1 -> T2 -> T3 ->Tn), I have found that divide and conquer is a lot faster, joining 2 tables at a time and creating a third table as a result set to join the next table to, etc. If the joins are in "parallel" (T1 -> T2 and T1 -> T3 and T2 -> T4, etc.) then it depends, and it is often necessary to experiment. I would try brute force first in this case.

"Tushar Mahapatra" <tushar_mahapatra_at_yahoo.com> wrote in message news:1ad0000.0201040705.651f411_at_posting.google.com...
> I have a question about what would be the best way to query an SQL
> database (I am using Oracle) where many parent and child tables are
> involved. I seek opinions on which of the following two methods would
> require the least execution time:
>
> Method 1 - BruteForce method:
> Execute a single query where all the parent and child tables are
> joined and all the data is returned at once. For example:
>
> select *
> from ParentTable P, Child1Table C1, Child2Table C2, ...
> where P.Child1Key = C1.Key and P.Child2Key = C2.Key and ...
>
>
> Method 2 - DivideAndConquer method:
> Execute more than one query. The first query gets data from the parent
> table:
>
> select *
> from ParentTable P
> where ...
>
> Next, in the front-end code, iterate through the parent table data
> received and execute queries into the children tables:
>
> select *
> from Child1Table C1
> where C1.Key = varPChild1Key and ...
>
>
>
> I am inclined to believe that the DivideAndConquer method would be
> faster, but I invite your comments, especially with respect to Oracle
> 9i.
>
> The scenarios I have described are actually much simplified
> representations of my situation.
Received on Fri Jan 04 2002 - 20:02:27 CET