Alan,

>> for select (blah, blah, blah)
>> do begin
>> execute procedure sp_process_one_detail( detail_pk );
>> count_processed = count_processed + 1;

A> just a comment here - if this first and only FOR SELECT is selecting the
A> 10,000 records in one go, then that will be the bottle neck to processing
A> these records.
A> If on the other hand you can construct any more FOR SELECTs in an
A> hierarchical fashion, such that the largest select is kept to within a few
A> hundred records, your processing performance will improve dramatically.
A> e.g. FOR SELECT subcatid WHERE CATEGORY=1 INTO :VAR DO BEGIN
A> FOR SELECT subsubcatid where subcatid=VAR INTO :SUBVAR DO BEGIN
A> FOR SELECT blah, blah WHERE SUBSUBCATID=SUBVAR DO BEGIN
A> now do your processing
A> END
A> END
A> END

I can't believe it would make a difference unless the single select
has a horrible plan and the three slices have good plans. Do you think
there may be any other reason?

Ciao
--
Nando Dessena
http://www.flamerobin.org
======================================================
I support Firebird, I am a Firebird Foundation member!
Join today at http://www.firebirdsql.org/ff/foundation
======================================================