Feedback
Did this article resolve your question/issue?

   

Article

How to improve Client Server Performance

« Go Back

Information

 
TitleHow to improve Client Server Performance
URL Name18342
Article Number000123114
EnvironmentProduct: Progress
Version: All supported versions
Product: OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
How to improve ABL Client Server behavior in OpenEdge?
How to improve 4GL Client Server Performance in Progress?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
This Article explains how Progress generates traffic on a Client/Server LAN or WAN environment.
It will focus on what Progress sends and receives through the network and not how one specific network protocol would handle it. In other words: concepts that are largely protocol independent.

Every FIND, FOR EACH, OPEN QUERY, GET, PRESELECT, ASSIGN, CREATE, DELETE (all data manipulation statements) generates client/server traffic when the database is connected with the -H -S client connection parameters. Being aware of the above and knowing how much traffic each 4GL/ABL statement generates can make the difference in a fast versus a very slow networked application.

SIMPLE FIND
 
FIND customer WHERE cust-num = 10 NO-LOCK.
No locking is involved.
One round trip to retrieve the record:
Client ---> Server Requests record
Server ---> Client Sends record back
One more one way message to release the cursor:
Client ---> Server Releases the cursor

GENERIC FIND
 
One round-trip to request record/get the record
No extra message to request a lock (RECID finds don't allocate a cursor). 
If the cursor will not be used again it is released immediately, if it might be used later it will only be released when the record goes out of scope which is done with a one way message.
SHARE-LOCKs are released with a one way message.
In a transaction no locks are released until the end of the transaction.

FOR EACH - NO JOIN & NO SORT
 
NO-LOCK
Fetch the first record with one round-trip then as many records as it fits on a message per round-trip. 
Can be hundreds of trips for small records and big Message Buffer Size (-Mm) for example. See Article: Is the number of records packed into a network message affected by the use of the NO-LOCK qualifier in a query   

NO-LOCK NO-PREFETCH
One roundtrip per record (not a very wise choice due to inefficiency).

SHARE-LOCK outside of a transaction 
One roundtrip per record plus a one way message to release lock.

Any lock inside of a transaction 
One roundtrip per record, as locks will be held until end of the transaction.

FOR EACH WITH SORT & NO JOIN

Will retrieve a list of RECIDs and sort keys as a prefetch FOR EACH regardless of sort type. 
For example: Small sort keys and a large Message Buffer Size (-Mm) parameter may pack hundreds (or thousands) of records on a single network message. 
Locks are acquired and released automatically by the server.
Progress then sorts on the client side and behaves like a regular sort but can't do prefetch.
Client sends RECID by RECID as the server returns each record.
If SHARE-LOCK outside of a transaction a one way lock release message is needed for each record.

FOR EACH WITH JOIN & NO SORT
Prior to OpenEdge Release 12.2 this behaves like nested FOR EACH statements and will generate the same traffic as:
FOR EACH customer SHARE-LOCK:
    FOR EACH order OF customer NO-LOCK:
    ...
    END.
END.
In 12 the Server Side Joins feature was introduced.  Now queries with joins are resolved in a single trip to the server, which uses multiple threads in the database server (_mtprosrv) to analyze.
 
FOR EACH WITH JOIN & SORT

Can use a lot of round trips,
Not recommended, use nested FOR EACH statements preferably.
Depends if the sort is only on the main table or not, for example: Query performance with a join is very poor with client/server connection

OPEN QUERY
 
Will generate no additional traffic if SORT or PRESELECT isn't needed.
Will behave like the SORT phase of a FOR EACH if SORT or PRESELECT is required.
Then one round trip per GET (if the requested record is already on the queries cache, then no traffic will be generated).
A REPOSITION statement can behave like multiple GET statements or like a single one depending on whether the query is using INDEXED-REPOSITION.

CREATE
No traffic right away.
When the first key is filled will:
Physically create the record with one round-trip.
Another round-trip to create the key.
Each extra key will use another round trip even if the whole record is created within a single ASSIGN statement.
Then will use another round trip to write the record when it is released.

UPDATE

Records are retrieved according to the rules presented before.
One round trip to update each key as needed even if all key changes are done within the same ASSIGN statement.
One round trip to write the new record.

DELETE

One round trip to delete the record.
One round trip to delete each key.

UPGRADE FROM SHARE-LOCK TO EXCLUSIVE-LOCK

Requires one additional round trip.

WHEN THE RECORD IS BIGGER THAN THE Message Buffer Size (-Mm) PARAMETER

Progress will send one way messages until the end of the record is finished so the record is efficiently streamed through the network: What is the -Mm parameter?
At the IP level, packet sizes are limited to the MTU for the connection which is determined by the Ethernet frame size the network interface can handle. Larger TCP message must be split into several Ethernet frames not larger than the MTU. TCP will break it up into the MTU size packets and send them out using TCP windowing.  There are some theories that -Mm should equal the MTU network setting to be as efficient as possible.
Apart from -Mm, further database startup parameters were added in OpenEdge 10.2B06 and 11.1 to tune Network Communication performance. See Article Parameters to tune Networked Communication.   
OpenEdge 12 to use the multi-threaded model: (-threadedServer -threadedServerStack ) where remote client requests are processed concurrently and use server-side-joins (-ssj) to further improve reducing the result set over the network for final client-side processing. Improvements for dynamic queries were added in later OpenEdge 12 versions.

TRANSACTIONS AND TRAFFIC
 
No traffic to open a transaction.
One round trip to commit a transaction.
A transaction UNDO is one round trip plus one extra one way message.
Sub-transaction UNDO will do the inverse operations which can generate a lot of traffic.

EXECUTION IN CLIENT/SERVER

At each initial reference to each database table (without using schema cache):
One round trip to retrieve mandatory array, template record and ALL index information.
There is a schema lock that needs to be acquired in shared mode to be able to run any procedure that uses the database.
Any time a program that references a database runs, it will attempt to acquire a shared schema lock with one round trip plus one one way message to release it in the end of the program.
  
CONNECTION AND TRAFFIC
 
Client/Server connections can generate a lot of traffic, how much traffic that gets generated on connection depends on how many database objects are in the Database.
Using a local schema cache (SAVE CACHE and the -cache startup parameter), reduces the connection traffic to a couple of round trips and less data. Further information is summarized in the following Article: How do I create a Schema Cache file?   
 
 
CHECKING TRAFFIC WITH PROMON
 
Optimal Client/Server performance metrics especially before deploying on a WAN
Run a controlled typical application test and record direct traffic metrics for the application. 
Start a database in client/server mode having only one remote user connected to it.
An overview of Database Server-Side activity while running the query:
PROMON > 5. Activity
The bytes sent between clients and the database can also be monitored with Network utilities such as tcpdump, Wireshark or with PROMON:
PROMON > R&D > 2. Activity > 2. Servers screen 
View how many messages were received from the client and sent back to the server. 
Time slices for example record the number of query time slice switches. A time slice switch occurs when the server moves to processing the next client without completing the query
These metrics can be reset between runs with:  proutil dbname -C zerostats  Further detail are provided in the following Article: Can PROMON and VST counters be reset to zero while the database is online?

THE APPSERVER ADVANTAGE
 
It takes just a couple of round trips to establish an AppServer connection and once this is established it is only one network round trip to execute each procedure that is: RUN ... ON SERVER.
For every RUN ...ON SERVER:
The client sends a stream of messages (usually only one) with the input parameters.
The server executes the procedure and sends back a stream of messages with the output parameters.
While there are no parameters for regulating packet size for the client to the Appserver, Progress 9.1D08 introduced a new client switch "-mc" that allows the message between the client and the AppServer to be compressed:
How to enable message compression between the OpenEdge client and AppServer?
Workaround
Notes
Progress Articles:

Progress Behavioral Differences Within a LAN vs. WAN 
Is the number of records packed into a network message affected by the use of the NO-LOCK qualifier in a query
What is the -Mm parameter?
Checklist for client performance fine tuning   
PASOE: Inconsistent response times on calls   

References to Other Documentation:

OpenEdge Programming Interfaces : Database Access : Joining tables : Server-side join processing
https://docs.progress.com/bundle/openedge-programmimg-interfaces/page/Server-side-join-processing.html

 
VOCABULARY for this Document

BANDWITH
How much data can be sent through a network at the same time.

DELAY
How long the information takes to travel through the network.
Usually terrestrial links have low delay while satellite links have high delay.
A congested network can have a high delay as well because of the need for messages to wait until the next hop is available to send the message.

ROUND TRIP
This is directly determined by the delay of the network links, not by bandwidth.
Every time the client sends a network message and waits for an answer the message needs to travel all the way from the client to the server and back. In a slow WAN, this can take as much as a couple of seconds. This a critical unit that is usually outside of the developer's control and that will make all the difference in performance.

PROTOCOL

TCP/IP
Is intended for the Intranet/LAN and is the fastest.

HTTP
Is slower than TCP/IP  due to wrapping of the ABL packets and is intended for use where tunneling is required over the intranet/Internet.

HTTPS
Is slowest of the three protocols due to the added security/encryption (ssl)

UDP
Is used for communication to and from the NameServer only, all additional communication is done with one of the above 3 protocols.

NETBIOS
Not really a protocol, just an API that can be run over another protocol for example TCP/IP, SPX, NETBEUI

MESSAGE
When Progress sends data through the protocol, it looks at the -Mm parameter set (if none is set it takes the default: 1024 for TCP) and tries to fit as much data in that number of bytes as it can. If it succeeds in putting all of the data you are sending into the 1024 byte space then it then prepends the message with 20 bytes of data as a header and sends it through to the database. If it can't put all of the data into the defined message size (-Mm) it will send multiple messages each the size of the -Mm parameter + the 20 byte header. Depending on the situation - zero data might need to be sent so in type of situation only a 20 byte header will be sent. The -Mm is a maximum size limit. The buffer itself is not padded to extend to the maximum defined size. Depending on the Protocol implementation of the system the actual datagram size may be larger than the 20 byte header + the message size due to whatever additional data or encapsulation the OSI model requires.
Last Modified Date6/24/2021 6:53 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.