Select first 8000 skip 8000 * from ext_table

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of robertgilland
> Sent: Tuesday, 27 June 2006 12:56 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: Progress of a Stored
> Procedure Feedback
>
> 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
> >
>
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> See what's inside the new Yahoo!
> Groups email.
> http://us.click.yahoo.com/2pRQfA/bOaOAA/yQLSAA/67folB/TM
> --------------------------------------------------------------
> ------~->
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>