At 12:03 PM 27/06/2006, you wrote:

>I have this stored procedure that injects data from
>an external table into my database.
>It seems to take forever.
>Is there any way in PSQL I can get this stores procedure to
>give me feedback on its progress?
>
>CREATE PROCEDURE PROC_EXT_FIELD3SALES
>AS
>DECLARE VARIABLE THEFIELD1 VARCHAR(10);
>DECLARE VARIABLE THEFIELD2 TIMESTAMP;
>DECLARE VARIABLE THEFIELD3 VARCHAR(14);
>DECLARE VARIABLE THEFIELD4 FLOAT;
>DECLARE VARIABLE THEFIELD5 FLOAT;
>DECLARE VARIABLE THEFIELD6 FLOAT;
>DECLARE VARIABLE THEFIELD7 FLOAT;
>DECLARE VARIABLE THEFIELD8 FLOAT;
>BEGIN
> FOR SELECT CAST( RTRIM( FIELD1 ) AS VARCHAR(10) ) AS FIELD1
> ,CAST( FIELD2 AS TIMESTAMP ) AS FIELD2
> ,CAST( RTRIM( FIELD3 ) AS VARCHAR(14) ) AS FIELD3
> ,CAST( FIELD4 AS FLOAT ) AS FIELD4
> ,CAST( FIELD5 AS FLOAT ) AS FIELD5
> ,CAST( FIELD6 AS FLOAT ) AS FIELD6
> ,CAST( FIELD7 AS FLOAT ) AS FIELD7
> ,CAST( FIELD8 AS FLOAT ) AS FIELD8
> FROM EXT_FIELD3SALES
> INTO :THEFIELD1
> ,:THEFIELD2
> ,:THEFIELD3
> ,:THEFIELD4
> ,:THEFIELD5
> ,:THEFIELD6
> ,:THEFIELD7
> ,:THEFIELD8
> DO
> BEGIN
> INSERT INTO FIELD3SALES
> (FIELD1
> ,FIELD2
> ,FIELD3
> ,FIELD4
> ,FIELD5
> ,FIELD6
> ,FIELD7
> ,FIELD8
> )
> VALUES
> (:THEFIELD1
> ,:THEFIELD2
> ,:THEFIELD3
> ,:THEFIELD4
> ,:THEFIELD5
> ,:THEFIELD6
> ,:THEFIELD7
> ,:THEFIELD8
> );
> WHEN SQLCODE -803 DO
> BEGIN
> UPDATE FIELD3SALES
> SET FIELD4 = :THEFIELD4
> ,FIELD5 = :THEFIELD5
> ,FIELD6 = :THEFIELD6
> ,FIELD7 = :THEFIELD7
> ,FIELD8 = :THEFIELD8
> WHERE (FIELD1 = :THEFIELD1 )
> AND (FIELD2 = :THEFIELD2 )
> AND (FIELD3 = :THEFIELD3 )
>;
> END
> END
>END;

There is no way for an executing procedure to send anything to the client.

I suspect your performance problem has everything to do with the size
of your batches. Rewrite the procedure so that it counts the
processed rows as it goes along; make rows_processed_so_far an input
variable and batch_rows_processed an output variable. In the client,
update rows_processed_so_far by the value of batch_rows_processed each time.

Organise things so that your batches are no larger than ~ 8000
records. Hard-commit after each batch completes. The procedure
should step through its For loop until it reaches the
rows_processed_so_far count and then start processing a further ~ 8000 rows.

As long as you have a broad estimate of how many rows there are in
the whole import, you can keep a progress bar going in your app that
is updated each time the client updates rows_processed_so_far. From
private correspondence I think you are typically dealing with 300K to
500K records in each import. That gives you about 39 steps for your
progress bar (and the title of a famous novel by John Buchan!).

The benefit of batching is that the undo log is kept within
reasonable bounds of resource usage *and* the rows in the destination
table that are updates will be garbage-collected sooner. With the
largest batches of the current dimensions you are really tempting
fate, given the size of the destination table....

./heLen