> Dear All,
>
> I've recently wrote a stored procedure that took sometime to complete,
> since it's processing (deleting and inserting) quite a lot of data. On
> average, it will process around 10,000 records per run, and took around
> 7-10 minutes to complete.
>
> The structure of the stored proc is something like this :
> ...
> create procedure sp_process_all( pk bigint )
> returns (
> status integer,
> count_processed integer,
> count_failed integer
> ) as begin
> for select (blah, blah, blah)
> do begin
> execute procedure sp_process_one_detail( detail_pk );
> count_processed = count_processed + 1;
> when any do begin
> status = -1;
> count_failed = count_failed + 1;
> end
> end
> end

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.
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.
e.g. FOR SELECT subcatid WHERE CATEGORY=1 INTO :VAR DO BEGIN
FOR SELECT subsubcatid where subcatid=VAR INTO :SUBVAR DO BEGIN
FOR SELECT blah, blah WHERE SUBSUBCATID=SUBVAR DO BEGIN
now do your processing
END
END
END

> ...
> create procedure sp_process_one_detail( pk bigint )
> as begin
> //get data,
> //process - calc
> //post data
> end
> ...
>
> The thing works ok, i suppose. The problem is in the user interface. I
> have a button on screen (Delphi) that will execute the sp and shows the
> result (how many succeeds/failed, etc) afterwards, but pushing this
> button means the user is going to have to stare at the hourglass cursor
> for minutes without any visual cues.
>
> Can anyone offer a better suggestion on how to improve this?
>
> One approach i'm thinking about is to get rid of the outer stored
> procedure, and call the inner stored procedure from the client side....,
> something like the following (in delphi-pseudocode):
>
> ...
> var
> query : tdataset;
> i, max : integer;
> ...
> //get all pk to be processed...
> query.close;
> query.sql.text := 'select pk from blah blah ....';
> query.open;
>
> //get recordcount
> ...
>
> //loop through all the pk
> while query.eof = false do begin
> //process one record
> execute procedure sp_process_one_detail(query['pk']);
> query.next;
>
> //show progress bar here...,
> end;
> ...
>
> So basically i'm rewriting the outer sp in delphi code, but this smells
> 'funny' to me :D. Any ideas will be appreciated.
>
> Thank you very much in advance.
> sugi.
>

see how the performance improves first witht eh suggestion above, often I
have done this and been so surprised at how fast the processing is, that I
have needed to prove to myself that processing actually took place... and
feedback is unwarranted.
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)
Alan