So can I do
select * from ext_table from row 8000 to 16000?

Regards,

Robert


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

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