Query performance: DivideAndConquer vs. BruteForce
Date: 4 Jan 2002 07:05:17 -0800
Message-ID: <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 - 16:05:17 CET