> just a comment here - if this first and only FOR SELECT is selecting the
> 10,000 records in one go, then that will be the bottle neck to processing
> these records.

Understood.

> If on the other hand you can construct any more FOR SELECTs in an
> hierarchical fashion, such that the largest select is kept to within a few
> hundred records, your processing performance will improve dramatically.

Will keep this in mind just in case the situation pops up in the future.
At the moment, unfortunately, my outer FOR SELECT is only selecting one
indexed field from one table.

> Otherwise you are best doing the FOR select stuff at the client side and
> calling the final processing SP also from the client so you will know
> exactly the stage of processing (for feedback)

After reading the replies, I'm now trying a slightly different approach.
I modified the sp so that it will return one record (SUSPEND) for every
record processed. Previously, it only do one SUSPEND after the FOR
SELECT finishes.
...
for select (blah blah blah)
do begin
execute procedure sp_process_detail(detail_pk);
count = count + 1;
suspend;
end
...

*IF* the stored procedure processes this line by line, then my UI issues
can be simplified, the screen only has to fetch and display the most
recently returned row from the SP. Something like this :
...
query.sql.text := 'select * from sp_process_all(pk)';
query.open;
while query.eof = false do begin
//1. get data
get_one_row_from_the_query();

//2. feed to UI
progress_screen.advanceOneTick();
//or progress_screen.displayProgress(query['count']);
application.processMessages;

//3. and so on...
query.next;
end;
...

Right now it seems to be working ok. Theoretically this should be
somewhat slower than the original approach (not to mention the
additional network traffic from fetching thousands of records *grin*). I
should probably use a 'unidirectional' data access component for the
loop...

Right now I'm a bit concerned about the scenario where the fetching
operation got interrupted... which means the process will stop mid-way
before all records got processed... any thoughts? Should i go ahead with
this approach?

Thanks in advance.
sugi.


PS:

Just in case anyone's interested, I'm testing the second approach on a
smaller dataset on my development machine: AthlonXP2500,Firebird 1.5.2
Win32, 512MB RAM, Fresh database.

1292 records processed in the FOR SELECT.
1292 records inserted into the MASTER table.
6323 records inserted into the DETAIL table.
Tested three times, elapsed time between 19 to 23 seconds.