Re: [sqr-users] Insert -SQR

[Alan.Ljungberg@cnpl.enbridge.com]

First thing you should determine is where the bottle neck is in your SQR.
It may very well be the insert command that sounds likely from the size of
the table.
On a doing many inserts into a table with a high row count will certainly
be slower when the index gets updated on each insert.  Dropping all the
indexes for the target(insert) table before inserting your rows will speed
the insert process.  Then rebuild the indexes when the inserting is
completed.  If the SQR is processing row by row doing a series of single
row inserts see if you can rewrite to an INSERT getting values from a
Sub-Query.

Below is an INSERT SQL with a few examples of tools to speed the process.
This is an example for Oracle but I would assume other DB's have similar
tools.
**NOTE if you explicitly define indexes as they are below you maybe in
trouble if someone adds an index or renames.  (Of this isn't tested but it
goes something like this....

Hope this helps somone out anyway.
-Alan.

Begin-Program
  Do Prep-for-insert
  Do insert_PS_WHATEVER
  Do rebuild-indexes
End-Program

Begin-Procedure Prep-for-insert
!*******************************************************************
!  ALTER SESSION to ensure Oracle assigns optimus
!  memory for sorting and memory intensive operations.
!  Check with your DBA for suitable values.
!******************************************************************
BEGIN-SQL
ALTER SESSION
SET SORT_AREA_SIZE = 40000000
END-SQL
!*******************************************************************
! DROP INDEX will allow for faster inserts since the index
! will not have to be updated on each row insert
!  Make sure you drop ALL indexes on the target table.
!******************************************************************
BEGIN-SQL
   DROP INDEX SYSADM7.r_whatever_idx1
END-SQL
End-Procedure !Prep-for-insert


BEGIN-PROCEDURE insert_PS_WHATEVER
!*************************************************************
!  Parallel HINT added will tell oracle to do a Parallel insert
!  Parallel insert usually faster **Check with your DBA for
! Correct values to apply for your DB. (N/A before v8)
!**************************************************************
Begin-SQL
INSERT /*+ PARALLEL([$TBLOWNER].PS_WHATEVER,4,1)*/
INTO [$TBLOWNER].PS_WHATEVER
(EMPLID,
EMPL_RCD,
EFFDT,
EFFSEQ,
DEPTID,
JOBCODE,
POSITION_NBR,
POSITION_OVERRIDE,
POSN_CHANGE_RECORD,
EMPL_STATUS,
ACTION)
SELECT
EMPLID ,
EMPL_RCD ,
EFFDT ,
EFFSEQ ,
DEPTID ,
JOBCODE ,
POSITION_NBR ,
!*******************************************************************
!  DECODE statement allows you to convert simple values
!  on the fly.
!*******************************************************************
DECODE(ELIG_CONFIG1,'S','SINGLE',
                    'C','COUPLE',
                    'F','FAMILY',
                    ELIG_CONFIG1),
!**********************************************************************
!  CASE statement allows you to do If-then-else processing
!  On the fly.  Very powerful function. (N/A before v8) I think
!**********************************************************************
(CASE WHEN ANNUAL_RT > 0 THEN ANNUAL_RT/260 ELSE 0 END),
HOURLY_RT
FROM [$TBLOWNER].PS_PS_SOMESUCH
End-SQL

End-PROCEDURE ! insert_PS_WHATEVER

Begin procedure rebuild-indexes
!*********************************************************
!  Don't forget to rebuild indexes after your done.
!*********************************************************
BEGIN-SQL
   CREATE INDEX [$TBLOWNER].r_whatever_idx1 ON [$TBLOWNER].PS_WHATEVER
      (EMPLID, EMPL_RCD, EFFDT, EFFSEQ)
      /*+ PARALLEL([$TEMP_OWNER].PS_WHATEVER,4,1)*/ TABLESPACE
[$INDEX_TBLSPC]
END-SQL
End-procedure !rebuild-indexes




                                                                                
                                       
                    Smyth Lisa G                                                
                                       
                    Contr AEDC/ACS          To:     sqr-users@sqrug.org         
                                       
                    <Lisa.Smyth@arno        cc:                                 
                                       
                    ld.af.mil>              Subject:     [sqr-users] Insert 
-SQR                                       
                    Sent by:             Expire On:                             
                                       
                    sqr-users-admin@                                            
                                       
                    sqrug.org                                                   
                                       
                                                                                
                                       
                                                                                
                                       
                    10/31/2002 12:30                                            
                                       
                    PM                                                          
                                       
                    Please respond                                              
                                       
                    to sqr-users                                                
                                       
                                                                                
                                       
                                                                                
                                       



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