Query performance with a join is very poor with client/server connection

Query performance with a join is very poor
Query with a join run via a client server connection can be very slow.
Query with a client server connection can be up to 10 times slower than a shared memory connection.
A dynamic query performance is slow when run client server.
 

Example ABL Query with joins:

vcQry = "FOR EACH RoleResource FIELDS(RoleResource_ID Role_ID) NO-LOCK"
                      + " WHERE RoleResource.Role_ID = 7328,"
                      + "FIRST Resources FIELDS(Resource_ID) NO-LOCK"
                      + " WHERE Resources.Resource_ID = RoleResource.Resource_ID".

 

This is expected behavior. It is due to the way query resolution is currently designed to work when a join is involved.

• When there isn't a join and records are read NO-LOCK, the server can "read ahead" and pack lots of records into a single message. 
• When there is a join and records are read NO-LOCK, table records are interleaved. The "read ahead" is limited to only those records that belong to a given parent.  This results in much more network activity and subsequently there is less advantage from the groupings of records per message. This can also exhibit higher database reads. 

In some situations improvements in performance can be made using one or more of the following techniques:

1. Modify the query to use PRESELECT instead of FOR
2. Modify the query to use field lists.
3. Modify the application to use an AppServer. Run the database and the AppServer on the same machine so the AppServer can use a shared memory connection. Run the query in a procedure that runs on the AppServer and returns the required records to the remote client as a TEMP-TABLE.

 

Progress Articles:

How to improve Client Server Performance  
OpenEdge 4GL Query concepts (FOR EACH, FIND, GET, INDEX) 
How Can the Efficiency of ABL Query statistics be verified?
What does the XREF WHOLE-INDEX tag mean?  
Why does a scrolling query generate more database reads in Client-Server mode?