Re: SQR Performance Issues (New Question)

[ANDERSD2 <ANDERSD2@OAKWOOD.ORG>]

My $0.02 worth regarding SQL/SQR performance...
1.  Simple is almost always quicker than complex.  The greater number of
tables/records in your join, the slower it will become.  Also, the more
difficult to maintain.  So, carefully look at your SQL and try to break it
into several/multiple procedures.  Keep each procedure/SQL to a topic.  For
example, instead of getting employee JOB data and joining that to JOBCODE
for the job description, build a procedure to extract JOBCODE information
once you have extracted the employee.  This is a derived example as
PeopleSoft already has a nice procedure to do just that.
2.  Outer Joins are bad.  No two ways about it.  Also, I have found the
utilization of EXISTS and similar statements to be hard on performance.  A
good example is an employee listing and their dependents.  Because not all
employees have dependents many developers utilize an outer join.  I find it
simpler/quicker to break the select for dependents into another procedure.
3.  When joining records be sure to join ALL common key fields before adding
your criteria.  Do not leave a  join incomplete by not ensuring the key
fields are tied together.  Once the keys are tied you should add your
criteria.
4.  Try to avoid ORDER and GROUP BY statements.
5.  Make your selection criteria as simple as possible.  Do not try all
sorts of fancy stuff in your WHERE clause.
6.  LET vs MOVE is a personal preference, though I am sure some fanatics
will say MOVE is more efficient.  However, in the grand scheme, probably
doesn't have a huge impact.
7.  Modularize your code as much as possible (keep it simple).  Start with
the record/join that will yield the greatest number of rows and then call
your procedures to continue parsing out the data.  For example, grabbing
data from the PAY records you would probably build different procedures to
extract EARNINGS, DEDUCTIONS, etc... after you have identified the PAYCHECKS
for the PAY_END_DT you are examining.  This goes  hand in hand with points 1
and 2.
8.  LOOKUPs and ARRAYs are nice as the retrieval of data is very quick as it
is stored in memory.  Use these instead of temporary tables (if possible).
9.  Become familiar with the PeopleSoft supplied SQCs (if you are a PS
client) as their code is easier to call (and usually quicker) than trying to
rebuild it on your own.  Don't re-invent the wheel if you don't have to.
10.  Experiment and have fun :)

-----Original Message-----
From: Sarah Jane A. Lohrey [mailto:lohreysj@MUOHIO.EDU]
Sent: Friday, March 09, 2001 3:40 PM
To: SQR-USERS@list.iex.net
Subject: SQR Performance Issues (New Question)


Hello Everyone,

I was watching all of your postings with regard to Deepak's questions on
SQR Performance Issues and I have a new question for you.


I consider myself a novice at SQR yet I have been asked to give a 3 hour
workshop to discuss performance issues in SQR programming to a small group
of users who are even newer to SQR than I am.   I'm hoping some of you can
add to my list of "Performance issues" to consider.


The kinds of things I have been think of so far include:  (NOTE:  THERE ARE
SOME QUESTIONS IN MY LIST)

1. From our most recent postings about using count(*) in subqueries.  There
are more efficient ways to write subqueries.
2. When using evaluate statements, one should use break logic and evaluate
the values that are most frequent first so that the break logic will exit
an evaluate sooner.
3.  When performing selects to multiple tables, does the order of the
fields in the select affect performance?  Perhaps the order of the tables
in the from clause or the where clauses affect the performance.   Do you
want the smaller tables listed first or last?
4. Is it true that the LET command is less efficient than the MORE command.
5.  ....

What are some of the most common consideration you have when attempting to
develop efficient programs?


Thanks in advance for any thoughts you might have on this subject.

Sarah



Sarah Jane A. Lohrey
Programmer
Miami Computing & Information Services
PH: 513.529.6074
FX: 513.529.1434
lohreysj@muohio.edu