[sqr-users] Tuning for inserts after each field selected

["Braswell, Michael" <Michael.Braswell@usi.net>]

Hello,
 
We are processing records from a data file and inserting them into PS
payroll.  The data file is arranged in a fashion similar to the
following:
 
"emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin
gscode3".... (there are 18 earning codes!)
 
so data looks like
 
0123,8,4,5,...
0234,7,3,6,...
 
We have to do validation on each earnings code and then insert into the
pay_others table (which is setup as having one earnings code per
record). So the file does not really match the database structure but we
are trying not to change the datafile.   Please look at the following
piece code and you will see that after we select for instance
"hoursforearningscode1" we do an insert into pay_others same thing for
all the other earning codes.  So we may do a total of 18 inserts for one
select if the person has hours in each earnings code.  I've only
included a piece of it but the code continues in the same fashion for
all 18 earning codes.  Typically the SQR's I've written have the file so
that you do not have to do inserts after selecting a particular field.
Rather you would select the emplid and earnings code and do an insert.
Does anyone know if the way we are having to write the SQR (below) is
causing it to run in 3-4 hours?  Has anyone written a program using the
inserts after each field that performed efficiently. We are just going
over our options and wondering if this is causing the problem and maybe
we should change the file layout.  The procedures called below for
validation run instantly so we have eliminated this as the bottleneck.
Thanks in advance for any help.
 
 
Begin-Select
TP.EMPLID
TP.NEX_LEGAL_ENTITY
TP.NAME
PD.NAME
JOB.EMPL_STATUS
JOB.FULL_PART_TIME
JOB.LOCATION
 
  let $EMPLID            =  &TP.EMPLID
  let $NAME              =  &TP.NAME
  let $LEGAL_ENTITY      =  &TP.NEX_LEGAL_ENTITY
  let $FULL_PART_TIME    =  &JOB.FULL_PART_TIME
  let $EMPL_STATUS       =  &JOB.EMPL_STATUS
  let $NAME_IN_PS        =  &PD.NAME
  let $LOCATION          =  &JOB.LOCATION
 
  let $message = ''
  let #Errors-This-Row = 0
  let $flags = ' '
 if $EMPL_STATUS = 'T'
  let $flags = 'T'
 else
  let $flags = ' '
 end-if
 
  add 1 to #Total-Records-Read
 ! let #Row = Edit(#Total-Records-Read,'99999')
! Validate values
 
  if #Errors-This-Row = 0
    do Validate-Payline                 
  end-if
 
 if #Errors-This-Row >= 1
  add 1 to #Total-Error-Rows
  add 1 to #array_count
  Put $Legal_entity $location $emplid $Name_in_ps $Name '  ' 0 $Message
Into Errors(#array_count)  
 else 
  Do Pay-Ern-Update
 end-if
 
TP.NEX_HRS_REG
  Let $erncd = 'REG'
  Let #Hours_in = &TP.NEX_HRS_REG 
 let #O_amount  = round(#Hours_in, 2)
 #debug show 'amount= ' #O_amount
   Let #Hours = #O_amount
 if #Errors-This-Row = 0 and #Hours <> 0
  If $FULL_PART_TIME = 'F' !full/part time enhancement validation
9/25/02
     do Write-full-part-time-error  
  else  
    let #Row = #Row + 1
      do Insert-Pay-Oth-Earns     
  end-if  
   end-if
TP.NEX_HRS_SF1
  Let $erncd = 'SF1'
  Let #Hours_in = &TP.NEX_HRS_SF1
  let #O_amount  = round(#Hours_in, 2)
  Let #Hours = #O_amount
  if #Errors-This-Row = 0 and #Hours <> 0
   If $FULL_PART_TIME = 'P' 
     do Write-full-part-time-error     
  else 
    let #Row = #Row + 1
      do Insert-Pay-Oth-Earns    
  end-if  
   end-if
TP.NEX_HRS_SD0
  Let $erncd = 'SDO'
  Let #Hours_in = &TP.NEX_HRS_SD0
   let #O_amount  = round(#Hours_in, 2)
  Let #Hours = #O_amount  
  if #Errors-This-Row = 0 and #Hours <> 0
   If $FULL_PART_TIME = 'P' 
     do Write-full-part-time-error     
  else 
    let #Row = #Row + 1
      do Insert-Pay-Oth-Earns    
  end-if  
   end-if
 

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