The performance of a system is a function of both the Application and the System on which the Application runs. Although the distinction between the Application and System performance is somewhat arbitrary:
- Application Performance depends on code design and programming techniques, whereas
- System Performance depends on the Progress product design and any inherent system limitations of the Hardware, Operating System, and Network.
Examine the Application design and coding, memory, disks, CPU, and network management. Maximum performance is accomplished through better resource allocation, better programming, and better database design.
The following are not intended to provide an exhaustive list, but simply to highlight examples in the key areas described above.
Application design for maximum performance should focus on the following:
Make sure the right ones have been defined and that when you retrieve records, you use selection criteria (WHERE clause) that makes use of an index. Record selection that uses "MATCHES" does not use an index. Record selection that skips the first field in a multi-field index also does not use an index.
For example, if you request records that have a middle name of "Mary" when the index is made up of last_name, first_name, and middle_name, Progress has to read each record to answer your query. Use COMPILE with LISTING and COMPILE with XREF as a tool to analyze your code.
Keep the transactions small. This minimizes the .bi file writes, and ensures records that are part of a multiple user application are not locked for too long.
Interaction with peripherals is inherently a slow activity.
For example, if you need to display a counter during a repetitive process, you might choose to update the counter every 100 records rather than with every iteration.
- Use NO-UNDO on program variables when you DEFINE them
Program variables are written to the local before-image file (.lbi) if Progress needs to keep track of their interim values.
- Wherever possible, avoid recursion to minimize the stack management activity of the CPU
- Calls to the operating system in some environments require a memory management task that can slow down the application. If there is a way to accomplish the action without escaping to the OS, you should consider that alternative.
- Use HIDE and VIEW to minimize the number of active frames and reduce CPU consumption
- Limit the use of GLOBAL variables to reduce R-code program size
Don't declare the entire application's list of global variables in one include file and {include} it everywhere - declare only the ones you need in the child procedures
Memory management can be accomplished by making changes to the operating system and/or memory startup parameters. Examine each of the following startup parameters in turn:
If -D is set too low, an unexpected recompiling (of session compiles) and/or reopening/rereading of .R code files occurs. In general, raising -D keeps commonly used .R files in the .srt file, thus saving search/move/open operation overhead.
If you have large procedures or deeply nested procedure calls, you can use -mmax to increase the initial size of the execution buffer to reduce disk I/O activity required to swap segments to the sort file.
If you increase the local buffer, you can effect a modest performance gain because this lowers the frequency with which Progress consolidates free space in the buffer.
The size of the stack (an internal memory area used by Progress program modules) in 1K units. This parameter is used to avoid stack overflow errors that occur most likely when data definitions are load.ed for very large tables or use recursive programs.
- Speed Sort Startup (-TB) and Speed Sort Merge Number (-TM)
If you must use un-indexed field sorts (such as the sorts in work files), the -TB and -TM options speed up the sorting process.
- The -TB option controls the size of the blocks Progress sets aside for sorting.
- The -TM option controls how many of these blocks can be merged at one time.
These sort options are particularly important when you do an index rebuild (PROUTIL).
Disk management helps to spread the I/O work that your hardware has to do. Better use of your existing hardware also improves performance. Consider the following options:
By using the quick request option, you avoid a search of PROPATH each time procedure is executed with a RUN statement. This option is useful once an application is in production and the .p source code files are stable.
Disk arm contention is reduced if temporary file writes are made to one or more disks away from the .db, AI and/or BI files. If the disks are independent channels, parallel disk I/O can improve performance considerably.
- Buffers for Temp Tables (-Bt)
Allocates a buffer in memory to store TEMP-TABLE's. TEMP-TABLE's will only be written to disk once the size of your TEMP-TABLE's is larger than the area allocated by -Bt. -Bt is 512 by default, which is too small for most applications (especially those which rely heavily on TEMP-TABLE's). If you have memory available, consider increasing this parameter with -tmpbsize introduced in 9.1D07 (and later).
Networking options to consider:
Reference article: How to improve Client Server Performance, which explains how Progress generates traffic on a Client/Server LAN or WAN environment.
- Message Buffer Size (-Mm)
If your database records are large, increase this parameter to avoid record fragmentation. However, if the network works more efficiently with small messages, reduce -Mm and fragment larger records.
Prior to OpenEdge 11.6: The -Mm startup parameter must agree between client and server. When the -Mm value is used as a database startup parameter, all the client-server connections to that specific Database need to have the same Message buffer size (-Mm value).
OpenEdge 11.6 and later: It is no longer necessary to specify -Mm on the client side and when the client value of -Mm is specified it is only used as a suggestion during startup, the value specified on the database server side is used once the connection is made.
- Database startup parameters to tune networked communication
-prefetchDelay
-prefetchFactor <n>
-prefetchNumRecs <n>
-prefetchPriority <n>
-Nmsgwait <n>
These parameters were introduced in OpenEdge 10.2B06 and 11.1.0, to ease the cost of networked communication in large scale deployments. They enable fine tuning the behavior of message packing for prefetch queries as well as how often the poll() system call is triggered.
- Schema Cache File (-cache)
This parameter is used to reduce connection time when the client connects to a database over a wide-area network WAN. Progress stores the schema cache as a binary file, called a schema cache file, on a local disk. The client can then read the schema directly from the schema cache file. This parameter should be used when a large number of clients connect to a database simultaneously. For example, after a database shutdown and/or crash. For further information refer to Articles:
How do I create a Schema Cache file? How to keep the schema cache on the client up to date with the database schema? - Location of r-code resources on disc
Reading r-code across a network drive, especially when the drive reference is not mapped to a drive letter, can cause severe performance issues when initially reading each r-code file from disc. Consider keeping r-code local to the process, or at least store it in procedure libraries.
Since OpenEdge 12 the database was enhanced for both multi-threading and server-side joins.
Tools/Processes used to track down performance issues
- Since OpenEdge Release 10.x the LOG-MANAGER system handle (-clientlog startup parameter) can be used with various log entry types in order to narrow down which lines of code took the longest to execute.
- 4GLTRACE - At logging level 3 (e,g, 4gltrace:3, or -logginglevel 3, or srvrLogEntryTypes=4gltrace:3, or agentLogEntryTypes=4gltrace:3). This provides tracing of all RUN statements and Invocations of Classes and Methods, as well as showing the parameter information in/out and return values.
By monitoring time between these operations you can narrow down to a certain routine, but not necessarily the line of code.
- FILEID (File Access) logging will show access to files on the operating system that the application or the executable interacts with. If a file is taking too long to open, you may see an open but not a close of a file, which may lead you to the next clue.
- QRYINFO logging provides details about every query (FOR statement and QUERY object) is opened, closed, etc and whether it was efficient.
In this output you are looking for "WHOLE-INDEX" queries, queries with "Select by Client: Y" or "Client Sort: Y". Also, to ensure your query is not making sub-optimal use of an index you can look at the number of DB Reads versus the number of Records from Server.
If there are an inordinately higher number of DB Reads than Records from Server, this indicates you may be only using the first one to <n> components of the query, leaving a significant number of records to read through in order to find all of the available records that meet query criteria.
This is typically solved by creating a new index with the existing, or a subset, plus the needed fields.
- PROFILER system handle (-profile startup parameter) provides the ability to log the timing of line by line execution for ABL programs. The output of this can be consumed in the Developer Studio for OpenEdge tool or in the legacy ABL based interface available here.
Articles:
How to use the Profiler within OpenEdge Architect / Developer Studio for OpenEdge ?
PROFILER Object attributes and methods
