RE: [sqr-users] suggestions for best practicewith insertsinto temptable

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

Kevin, you keep talking about an elderly database, and since you're an
Oracle Forms Developer, I assume it's Oracle.  Which version are you using?
Fifteen thousand rows of five columns is small for a database table or a SQR
array, unless you're running on a very old computer.  What hardware do you
have?

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


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