Query performance: DivideAndConquer vs. BruteForce

From: Tushar Mahapatra <tushar_mahapatra_at_yahoo.com>
Date: 4 Jan 2002 07:05:17 -0800
Message-ID: <1ad0000.0201040705.651f411_at_posting.google.com>

[Quoted] [Quoted] I have a question about what would be the best way to query an SQL [Quoted] 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 - 16:05:17 CET