--- In firebird-support@yahoogroups.com, "robertgilland"
<robert_gilland@...> 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?

There are several ways you can achieve this, but any feedback will
slow down the process.

Here are some ideas:

* Create an output parameter called recordsprocessed, increment it
and suspend inside your for select loop. This will seriously slow it
down, so perhaps only suspend every 10000 records or so (like gbak).
If you have triggers in the picture, you may need to reduce the
number of records between suspends to give satisfactory feedback.

select RecordsProcessed
from PROC_EXT_FIELD3SALES(.....)

Would then return the progress.

* Use some UDF call during the for select loop. We have a custom UDF
that has a wrapper for OutputDebugString, so I can watch DebugView to
for a commentary of my stored procedures. This has the double benefit
in you can distribute a version of your UDF without the
OutputDebugString code in it, which means that you can leave it in
there (providing there is only a sensible amount, everything in
moderation).

* I assume you must have some generator based primary key field? If
so, you can poll the generator value whenever you get curious. If
not, you could consider incrementing a generator for each record in
your for select.

select gen_id(gen_whatever, 0)
from RDB$DATABASE

Will tell you your current number. This can be a good approach
because the engine does not wait for you to request a record.

* Delete the record from the external table as you go. External
tables are transaction independent. You may be able to inspect
progress this way (or may not, never tried such an approach).

* Split the external table into 'chunks'. You should be able to
achieve 10000 inserts per second give or take on moderate hardware,
so if the source is actually multiple files which you process one at
a time, this may be enough.

So it really depends on your needs, whether it is just a curious
guestimate of the progress or an exact percentage you want.

Adam

>
> 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;
>
> Regards,
>
> Robert.
>