Consultor Eletrônico

Status: Verified

GOAL:

How to improve Client Server behavior in Progress

GOAL:

How to improve Client Server Performance in Progress

FIX:

This solution explains how Progress generates traffic on a client/server LAN or WAN environment.

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 -N -H -S parameters.

Being aware of that and knowing how much traffic each 4GL statement generates can make the difference in a fast versus a very slow application.

What will be discussed in this document are concepts which are largely protocol independent.

This solution will concentrate on what Progress sends and receives through the network and not how one specific network protocol would handle it. As far as Progress is concerned the -Mm parameter is the only thing that matters for this solution.


SIMPLE FIND

FIND customer WHERE cust-num = 10 NO-LOCK.
One round trip to retrieve the record.

One more one way message to release the cursor.

Client ---> Server Requests record
Server ---> Client Sends record back
Client ---> Server Releases the cursor
Remember that no locking is involved.
NOTES REGARDING GENERIC FIND SITUATIONS

One round-trip to request record/get the record

No extra message to request a lock (RECID finds don't allocate a cursor). If Progress knows that the cursor won't be used again it is released right away, 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. Remember that 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 for small records and big -Mm. 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 small sort keys and a big -Mm parameter setting Progress can 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 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

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.
FOR EACH WITH JOIN & SORT

Depends on if the sort is only on the main table or not. Can use a lot of round trips. Not recommended, use nested FOR EACH statements if at all possible. OPEN QUERY

Will generate no 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).r>
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 round trip. WHEN THE RECORD IS BIGGER THAN THE -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. TRANSACTIONS AND TRAFFIC

No traffic to open a transaction.

One round trip to commit a transaction.

Subtransaction undo will do the inverse operations so it can generate a lot of traffic.

A transaction undo is one round trip plus one extra one way message. CONNECTION AND TRAFFIC

Regular client/server connections can generate a lot of traffic.

How much traffic that gets generated depends on how many tables are in the Database.

Anyone worried about traffic should use a local schema cache (see SAVE CACHE and the -cache startup parameter). This reduces the connection traffic to a couple of round trips and less than 10KB of data. EXECUTION IN CLIENT/SERVER

At each initial reference to each 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 schema lock with one round trip plus one one way message to release it in the end of the program.
CHECKING TRAFFIC "THE EASY WAY"

Start a database in client/server mode having only one remote user connected to it.

Use the PROMON R&D Activity: Servers screen to view how many messages were received from the client and sent back to the server, plus some extra stuff.

Just run this with a real test and you can take direct traffic numbers out of your application.

Customers worried about client/server performance should use this screen to take measures especially if they are going to deploy on a WAN. 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. .