Kbase P4737: How to improve performance with AS/400 dataserver
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
GOAL:
How to improve performance with AS/400 dataserver
FACT(s) (Environment):
Progress/400 DataServer
IBM AS/400 (RISC)
FIX:
Areas where performance can be improved for Progress/400 DataServer are:
I. Client/Server Startup parameters
II. DB Design
III. 4GL Queries
IV. Native 4GL Client
I. Client Startup
Allocation of RAM for Progress Objects
Example: -mmax 2048, this number is in Kb, it is allocated based on available memory, should also consider memory required for other applications
Memory for merging and sorting
Example TB 31(sorting) TM32 (merging)
Maximize values to minimize overflow to temp tables, value is allocated in kb
Location of temp files
-T c:\psc-temp
Network wide performance degradation if temp files are located on a file server
Locate temp files on a local hard drive
Allocated memory for temp tables
Example: -Bt 200
Message Buffer Size
Example: -Mm 4096
Major factor in performance
Default 1024 is almost always too small
Test with large values: 4096/tcp 4094/SNA
Compression of DB Messages
Example: -Dsrv COMPRESS=1
Off by default
Useful in slower networking environments
Requires each record to be compressed/decompressed
Selection by Server
Example: -Dsrv SBS=1
On by default
Turn off if system is CPU constrained
Startup summary:
Most client startup options do not effect Progress/400 DataServer but they can have a definite impact on performance
Startup options that do affect the client and server definitely can have a HUGE impact on performance
II. DB Design:
Following has the greatest impact on performance:
indexing
DDS
Client Schema Holder
indexing
1. when to add an index?
Any keyed sequence that is used often
Costly to have too many indexes
2. avoid using USE-INDEX
disables multiple index support
takes flexibility away from index selection algorithm
BUT, it may be required under certain conditions
3. Which index is being used
use xref option on the COMPILE statement
EXAMPLE: COMPILE pgm.w XREF pgm.xrf
Client Schema Holder
-required by 4GL client
-contains information about the data definitions of a DB2/400 database. This information includes a description of the DB2/400 database structure, tables, fields and indexes.
-it doesn't contain any user application data
-the progress/400 DataServer accesses the schema holder only when it compiles procedures and at the beginning of a runtime
III. 4GL Queries
Native vs Progress Locking
Controlled by the existence of PROLKT
Recommend PROGRESS Locking when:
Existing application that uses SHARE-LOCK's
Recommend Native Locking when:
New applications
Compatibility with other AS/400 applications
Field Lists
Specify a limited number of fields to send over the network
Only beneficial when a relatively small number of fields are specify
INDEX-REPOSITION
Supported in v9.0 and higher
Super performance
Use it when you can
DESCENDING INDEXES
When FOR EACH ...BY ...DESCENDING is used
Be sure that a DESCENDING index exists over the BY fields
Be sure to explicitly specify a Descending index by using USE-INDEX
WHERE CLAUSE
Use EQ first
Use ranges
Use matches sparingly
Use BREAK-BY sparingly
Varies depending on query
4GL Queries
only testing will determine how code will perform in your environment
use progress Editor to obtain times
time query with: TIME & ETIME
IV. Native 4GL Clients
Programs executed using Native 4GL client don't worry about the network
This makes processing to be much faster
IFS vs QSYS File System
QSYS has been around from the beginning
IFS has superior performance relative to QSYS
Use IFS whenever is possible
External Programming Interface
Allow OS/400 HLL programs to be called and passed parameters
Allow 4GL programs to be called and passed parameters from an OS/4000 HLL program