[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] Tuning for inserts after each field selected
If it's all inserts and no updates and you can run this during off hours, you
may want to drop the indexes first, do your inserts, then rebuild the indexes.
Bound to save you time.
Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: Braswell, Michael [mailto:Michael.Braswell@usi.net]
Sent: Thursday, January 02, 2003 11:49 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Tuning for inserts after each field selected
Hi,
Below is the from and where clause, sorry about that. I can double
check and see if they can live without the name from Peoplesoft in the
report. I suppose they just wanted to do a visual validation at this
time.
I suppose my concern was how we do an insert after each field selected.
Usually I would select all fields then do an insert. I thought because
this is not the way it's normally done it may cause SQR to perform
poorly. If anyone had run into this before I would suggest that we
change the data file and go from there.
The b-1000 is a good suggestion. In fact I learned about this a couple
weeks ago and tried it, unfortunately no significant improvement was
made in the completion time.
I do use an on-error routine, in which I write out the errors to a
report if anything happens. We are not getting any errors like this but
I will double check the report.
Here is the from and where clause. Thanks.
>From PS_NEX_TM_LOAD_TMP TP,
PS_JOB JOB,
PS_PERSONAL_DATA PD
where
JOB.EMPLID = PD.EMPLID
and PD.EMPLID = TP.Emplid
and JOB.EMPL_RCD# = 0
and JOB.EFFDT = (select max(EFFDT) from PS_JOB
where EMPLID = JOB.EMPLID
and EMPL_RCD# = JOB.EMPL_RCD#
and EFFDT <= $Pay_End_Dt)
and JOB.EFFSEQ = (select max(EFFSEQ) from PS_JOB
where EMPLID = JOB.EMPLID
and EMPL_RCD# = JOB.EMPL_RCD#
and EFFDT = JOB.EFFDT)
order by TP.NEX_LEGAL_ENTITY, JOB.LOCATION,TP.EMPLID
-----Original Message-----
From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us]
Sent: Thursday, January 02, 2003 12:38 PM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] Tuning for inserts after each field selected
You've left out the from and where clause of your selection, which may
be the place most open to improvement. There's not much you can improve
with inserts.
One thing I'd suggest is to not join PS_PERSONAL_DATA just to pick up
the "name in Peoplesoft". Is it so important to have that in addition
to the name in your input table? If you absolutely need it in the error
reporting, and you don't have many errors, call a subroutine to select
PS_PERSONAL_DATA for just the employees with errors.
Another improvement is to say "begin-select -b1000". That will buffer
1000 rows at a time rather than the default of 10.
Also, are you using "on-error" with your insert? If so, you may be
getting errors (i.e. duplicate rows), which take much longer to process
than successful inserts.
-----Original Message-----
From: Braswell, Michael [mailto:Michael.Braswell@usi.net]
Sent: Thursday, January 02, 2003 8:52 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Tuning for inserts after each field selected
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
_______________________________________________
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