Robert,

At 04:56 PM 27/06/2006, you wrote:

>Thank you helen,
>this is the final outline from the stored procedure.
>Unless there is a quicker way to get the select to go
>to the records we need.

You might like to try the "FIRST SKIP" approach to getting your set
for the loop, although I suspect there wouldn't be any
improvement. Still, you can't know till you try.

>CREATE PROCEDURE PROC_EXT_FIELD3SALES
>( ROWS_PROCESSED_SO_FAR INTEGER, ROWS_PERCYCLE INTEGER )
>RETURNS
>( BATCH_ROWS_PROCESSED INTEGER )
>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;
>DECLARE VARIABLE THECOUNT INTEGER;
>BEGIN
> THECOUNT = 0;
> FOR SELECT CAST( RTRIM( FIELD1 ) AS VARCHAR(10) ) AS FIELD1

OK, there is unnecessary processing in these selects.

1) You don't have to right-trim to get rid of trailing blanks on varchars.
2) You shouldn't need to cast the incoming CHARs as varchar. Try it
without and see if you can save some cpu time.
2) You shouldn't be using field aliases here, as they are
meaningless. All you want to do is pass values to variables. That's
extra cpu cycles you can avoid.

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

Review the logic in the procedure body. I can see some problems in it.

./heLen