[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] Performance help needed - where the time goes?
I'd be glad to elaborate, but please check whether your SQR developers have
experience with load-lookup or arrays. The higher your in-house knowledge,
the less I'll have to explain.
-----Original Message-----
From: yelena_kontorovich@baxter.com
[mailto:yelena_kontorovich@baxter.com]
Sent: Friday, October 31, 2003 8:55 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Performance help needed - where the time goes?
Steve,
Our bqrp099.sqr is a modified version of PER099 - we select additional
information from other tables. We are on HRMS 8SP1, tools 8.1.7.4. We
don't keep track of PER099 since 2 years ago when we've converted to HRMS
8SP1, but if you have specific advices, I'll really appreciate them, it
might be applicable to our program.
On suggestion 3 & 4 - could you give me examples or more explanation if you
have them handy? If not - I'll talk to our SQR developers.
Thanks,
Yelena Kontorovich
Technical Consultant
847.948.2927
yelena_kontorovich@baxter.com
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, re-transmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the content.
"Alexander, Steve"
<Steven.Alexander@sanj To:
"'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
oseca.gov> cc:
Sent by: Subject: RE:
[sqr-users] Performance help needed - where the time
sqr-users-admin@sqrug. goes?
org
10/22/2003 07:31 PM
Please respond to
sqr-users
Is this PER099? If so, what version of Peoplesoft is it and what is the
date of Peoplesoft's latest change to the program? If you're trying to
tune
up PER099, we probably have specific advice to get the best results most
quickly. If you've got a custom program, our advice will be more general.
People generally approach this type of task in four possible ways. First
(slowest), by taking each employee from PS_PERSONAL_DATA and reading just
his or her rows from the other ten tables. Second (maybe faster, maybe
not), with joins to get the rows from the ten other tables while you get
the
rows from PS_PERSONAL_DATA. Third (pretty fast), with load-lookups on the
other tables. Fourth, usually as fast as it gets, by reading the columns
you want from each table into a common array, where each employee has one
"row" of the array, and there is a field for each column you want to write
to PS_EMPLOYEES. I can expand on these approaches if you like.
-----Original Message-----
From: Ian Mills [mailto:Ian.Mills@orix.com.au]
Sent: Wednesday, October 22, 2003 4:29 PM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] Performance help needed - where the time goes?
Yelena,
It looks like you are talking about Peoplesoft. I have never used it in
the past but I thought I may be able to share some of the things I have
had to do in writing my SQR reports to improve efficiency.
Firstly, one thing I have noticed with SQR is that you can run a query
against any database outside of an SQR program and it may take 15sec,
but when you include it in your SQR, it may take a minute or more. Why
this is so? I do not know. You learn to live with this.
In the queries against db's like sybase & oracle etc., you can include
index hints to force the database to use a specific index on a table.
Sometimes the optimiser can get it very wrong. When running SQR against
Sybase especially, somehow in most of the reports, the queries wouldn't
use any indexes because SQR somwhow did something to the optimiser which
meant you had to include index hints, or the reports would take 10 to 20
times longer than they needed to.
Another trick you may want to consider if you haven't already. You say
you have to access 10 other tables, you can sort the data from
PS_PERSONAL_DATA in such a way that you can minimise the number of
queries to these other tables. All you would then do is add code to your
report to check if the value you are using to access these other tables
is different to the last row. If not, you don't need to access the table
again. This would be a significant saving of the number of queries being
executed is 80,000 x 10.
Is is possible to use LOOKUP tables in your program?
In my experience using the -B option, you have to work out a happy
medium between increasing the rows extracted to improve database
performance, at the same time, not making the value too large that you
can effect network performance. When I do need to use -B, I tend to use
around 250. Like you, I found that my report ran slower when I made the
vaule too large.
Is your report producing any SPF output? Any PDF output? If you have a
large output file and you are creating a PDF file as well, the time it
takes to produce the PDF file can be surprising. We have a report here
that runs for upwords of an hour and produces a report of over 1000
pages. 15 to 20 minutes of that run time is time spent producing the PDF
file.
I hope I have been of some assistance to you. Good luck and I hope you
don't have to spent too much more time on this problem.
Regards,
Ian Mills
ORIX Australia Pty Ltd
>>> yelena_kontorovich@baxter.com 23/10/2003 8:15:58 am >>>
Hello, everyone,
I need help with performance.
We have a program that runs for 2.5 hours nightly and we are running
out of
the batch window. Program select appr 80,000 employees from
PS_PERSONAL_DATA and PS_EMPLOYMENT and for every emplid it select
additional information from another 10 tables to update or insert the
record into PS_EMPLOYEES table. I ran explain plan and tkprof and I
have
(overall) 46 min of CPU time and 53 min of elapsed time. When I run
separate statements I'm getting time close to tkprof. If program runs
for
150 min, where the rest of the time is going? My DBA thinks that time
is
going to network or application. We've changes tnsnames.ora to have
PROTOCOL=BEQ which should user local connection instead of SQLNet - I
got
only slight improvement. Also I've played with -B flag: for -B100 I
got
the same time, for -B1000 time actually increased by 1 hour. I just
don't
see where the time is, how can it be in application. Do you have any
idea?
Any help is greatly appreciated, I've spent close to 2 week on this and
I'm
stuck.
Thanks,
Yelena Kontorovich
Technical Consultant
847.948.2927
yelena_kontorovich@baxter.com
The information transmitted is intended only for the person or entity
to
which it is addressed and may contain confidential and/or privileged
material. Any review, re-transmission, dissemination or other use of,
or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received
this in error, please contact the sender and delete the content.
_______________________________________________
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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users