Re: [sqr-users] suggestions for bestpracticewith insertsinto temptable

["David Thorburn" <thorburn@conl.net>]

Why don't you choose to run this on the server instead of the local client
workstation?

Regards,

David
__________________________________________________

David Otis Thorburn                                   (916) 712-2272
 thorburn@conl.net      www.geocities.com/david_thorburn
__________________________________________________

----- Original Message ----- 
From: "Kevin Laroche" <bydesign@magma.ca>
To: <sqr-users@sqrug.org>
Sent: Thursday, September 30, 2004 9:36 AM
Subject: RE: [sqr-users] suggestions for best practicewith insertsinto
temptable


> Thanks George
> The reports that we have rollback problems with run 15-20 minutes and do
10-12000 inserts.
> The elderly database is being upgraded but we dont want to have capacity
expand to fill
> the space available.
> I will try the /*+APPEND */ and batch commits within the SQR.  A global
table sounded
> good until the DBA pointed out that 1 or 2 users running the large reports
at the same
> time could cause problems for the other 100 users who are not putting any
sort of load on
> the database.
> Are there functional limits on the size of an array that SQR can hold?
> Is an array of 10-15,000 records with 5 columns held in a client memory PC
going to make
> their PC as slow as molasses?
>
> Does anyone else work with large in memory arrays in SQR?
>
> Thanks
>
> Kevin LaRoche
> Oracle Forms Developer
>
> On Sep 30, "George Jansen" <GJANSEN@aflcio.org> wrote:
> >
> > Rollback problems depend on the rate of database change and on the
> > (time) length of the query. Either you generated so much rollback that
> > you filled the segment, or your query lasted umpteen hours and Oracle no
> > longer has the rollback blocks to reconstruct what the tables looked
> > when your query started. If you are filling up the segment, then either
> > you need larger segments or you need to use a strategy, e.g. INSERT /*+
> > APPEND */ that generates less undo. If your query is running too long,
> > then maybe you need to rethink how it runs.
> >
> > In general you'll get better performance with Oracle doing
> > set-at-a-time processing:
> >
> > Begin-SQL
> >    INSERT INTO blah
> >          (x, y, z)
> >    SELECT a + b, c*d, e
> >    FROM w
> > End-SQL
> >
> > If you must do row-at-a time processing, then perhaps you could use a
> > GLOBAL TEMPORARY TABLE to hold the rows, then do a big INSERT /*+ APPEND
> > */ at the end.
> >
> > Need I mention that you can't use the GLOBAL TEMPORARY TABLE for the
> > next report? Even if created with ON COMMIT PRESERVE ROWS, the contents
> > disappear with your session.
> >
> >
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
>



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users