Re: Query performance: DivideAndConquer vs. BruteForce

From: <D_at_B.A>
Date: Fri, 04 Jan 2002 18:51:03 GMT
Message-ID: <rQmZ7.4149$cD4.7761_at_www.newsranger.com>


It depends how do you want to get the data in the front end. One relation? Then, almost certainly, competing with optimizer (DivideAndConquer) is a losing game for you. Nested structures like this:

class Parent {
Child[] children;
};

where you want to put the result into an array of Parent? Then you need to split your query into many, but don't fall into the trap of recursion: you'd better get the rows from the parent table first, and then all the detail records from the child table _in a single shot_. One more time: no explicit nested loops as you suggest, or your system will never scale.

In article <1ad0000.0201040705.651f411_at_posting.google.com>, Tushar Mahapatra says...
>
>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 - 19:51:03 CET

Original text of this message