RE: [sqr-users] Insert -SQR

[Amy Glodowski <aglodowski@assisted.com>]

Hi Lisa,

We've had luck speeding up our insert-intensive SQRs by issuing a "commit"
statement periodically (say after every 100 rows processed).  We use a
counter variable to increment to the "magic" number, perform the commit, and
reset the counter.

There are caveats with using the commit command so be sure to read the SQR
documentation.

Amy



-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Thursday, October 31, 2002 1:46 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Insert -SQR



Hi Lisa,

I agree with your consultant on dropping and rebuilding the indexes.  Also,
make sure you use indexed fields for your joins and use as many of them as
possible in both main and sub queries.

I have had some success speeding things up in loading values from small
tables used for lookup into arrays.  You just have to try this to see if it
will work for you.  Put a timer routine in your program and call it from key
points to see just where the slow up is.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: Smyth Lisa G Contr AEDC/ACS [mailto:Lisa.Smyth@arnold.af.mil]
Sent: Thursday, October 31, 2002 1:31 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Insert -SQR


I inherited this program.  I have been asked to improve performance because
it is taking a very long time. 


I have an SQR program that inserts a record into a table with over 8 million
records, needless to say this is very costly to the database(Oracle) as
noted in the EXPLAIN PLAN..   

The process reads a record from the source table and does some validations
and lookups of additional values and then inserts the record into the target
table then repeats the process by reading a record from the source table and
so on. 

Someone suggested disabling the index while inserting and then rebuilding it
after it is finished inserting.   

Any suggestions would be great or where to go lookup some additional info.
I have read some of the Oracle Database Performance Guide and Reference
material. 

Thanks, 

Lisa 


Lisa Smyth
PeopleSoft Financials
931.454.4861
Lisa.Smyth@arnold.af.mil

_______________________________________________
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

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