>>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.
> this seems a bit slow to me - I'd be determined to get it faster

At this point, I'm not sure where to start. The main bottleneck suspect
is the 'inner' stored procedure that does the actual inserts. I'm
copying the code fragments from my original posting :

...
create procedure sp_process_one_detail( pk bigint )
declare variable ...;
as begin
//1. fetch data,
select blah, blah, blah from .... into ...;

//2. insert data to master table,
insert into master (...) values (...);

//detail loop
for select blah, blah, blah
from detail
into blah, blah, blah
do begin
//3. process - calc
...
//4. post data
insert into detail (...) values (...);
end
end
...

This storedproc is called 1292 times in the timing benchmark above. Step
Number 3, the actual processing, is a fairly complex calculation,
involving a big switch-like statement:

...
if (id = 1) then begin
//calculation for case 1
end else if (id = 2) then begin
//calculation for case 2
end else if (id = 3) then begin
//calculation for case 3
end else if ..... // up to *TWENTY* different case
//calculation for case ...
end else begin
//calculation for the default case
end
...

Each of the 'case' might involve fetching data and performing some
calculations, and then storing the result into the local variables, to
be INSERT-ed later. I thought 'Refactoring' each of the calculation into
lots of smaller storedproc is bound to be slower, so i'm putting them
'inline'. If there's a different approach that could help with the
performance, i'd love to hear it.

Thank you very much in advance.
sugi.

PS: Is there an 'EVAL' function/UDF available for firebird?