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 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
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) PARAMETERProgress 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
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.
