Re: Query performance: DivideAndConquer vs. BruteForce

From: Andrew Wilson <andrew_at_blueoffice.com>
Date: Sat, 5 Jan 2002 16:57:44 +0100
Message-ID: <a177qa$2b1m$1_at_news.cybercity.dk>


[Quoted] As a rule of thumb - "The more you perform on the database server, (and the [Quoted] less on the client) the faster will be the overall response time"!

[Quoted] Performing a loop on the client, performing multiple queries will probably be a factor of many times slower.

[Quoted] [Quoted] In the full query case I would imagine that the server would perform a table [Quoted] scan on the parent table, and then perform full sort merges for each of the [Quoted] child tables (depending on table sizes, statistics on columns, table structuring, cleverness of optimiser, complexity of query, etc, etc). All [Quoted] data then returned across the network to the client.

In the "divide-and-conquer" case a table scan of the parent table is peformed and the data returned across the network (or locally).

Now we get into the trouble area, because for each row in the parent table [Quoted] we now perform a select from each of the child tables. As they are primary [Quoted] key selects, and I assume the table is structured on primary key, then (if [Quoted] btree/ISAM) a tree walk is performed on the child table. The total number of [Quoted] pages read will be greater (index pages need to be re-read each time [maybe [Quoted] buffered]), the amount of server CPU will be greater (each seperate query [Quoted] will have to be re-analysed), network traffic will certainly be greater.

At a total guess I would imagine that in the first case the selection is of [Quoted] a logN type, whilst in the second case it is more likely N to some power.

Stick to the server ......

Andrew

[Quoted] "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 Sat Jan 05 2002 - 16:57:44 CET

Original text of this message