Processing too much data in single stored procedure
can create huge undo-log, which can slow it down significantly.
On the other hand calling one procedure 10,000 times
sounds a bit crazy too. Perhaps you should do something in between,
e.g. process rows in batches of 100 rows ...

Ivan

----- Original Message -----
From: "sugi" <truesaint@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, March 09, 2005 3:16 PM
Subject: [firebird-support] Stored Procedure Usage for Batch Processing


>
> 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
> ...
> 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.