Server-side join is a feature in which certain queries involving joins, execute on the database server, rather than the client. Server-side join improves performance by reducing the amount of data transferred across the network and by reducing the computational cost on the client.

Background

Prior to OpenEdge 12.0, when the AVM executed a join of multiple tables, some records which did not satisfy all levels of the join may have been returned to the AVM client, which needed to perform additional processing to eliminate these extra records from consideration. This methodology provided a correct response to the ABL application but with performance and security challenges. Starting in 12.0, incremental functionality was added to perform joins of multiple tables on the database server. This functionality is known as server-side join.

Requirements for server-side join use

The following requirements must be met for a join query to be executed server-side:

  • The database server must be multi-threaded.
  • All tables to be joined must be in the same logical database.
  • There must be ten or fewer tables to be joined.
  • NO-LOCK must be used.
  • The ABL query construct must be one of the following:
    • FOR statement.
    • Dynamic query that does not use the INDEXED-REPOSITION option or involve an outer join.
Note: Although the query needs to be NO-LOCK for server-side join to be in effect, you can obtain records with a lock using the GET statement for a static query, or the desired GET method (for example, GET-NEXT()) for a dynamic query.

Restrictions

If all the requirements are met, there may still be some cases where server-side join is not used.

  • Server-side join is not used for static queries (DEFINE QUERY statement).
  • Server-side join is not used for queries with the following constructs on any child table:
    • User-defined functions, methods, or properties with a GET implementation.
    • CAN-DO function.
    • Expressions with MEMPTR and LONGCHAR data types.
    • TENANT-WHERE clause.
  • The use of RECID and ROWID to join records disables server-side join.
  • If a FOR EACH query contains a BY clause and the index used for the query provides the same sorting order specified the BY clause, the join in done on the server. However, if the table does not contain any indexes that provide the sorting specified by the BY clause, and the BY clause does not specify only fields for the most inner table of the join, then the join is done on the client.
    Note: You can tell whether the sort was done by index or not by looking at the "Client Sort:" entry in the query information log file (see Query plan). "Client Sort: Y" indicates a non-index sort and "Client Sort: N" indicates the sort was done by index.

    The following table shows different query examples and whether the join is executed on the server (On) or the client (Off).

    Query example Server-side join Sort by index Notes 
    FOR EACH Customer NO-LOCK,
  EACH Order OF Customer NO-LOCK,
  EACH OrderLine NO-LOCK OF Order 
  BY OrderLine.Price :

DISPLAY
  Customer.NAME OrderLine.Price Order.Carrier.
END.
    		 On No Using the BY clause on the last table’s field – server-side join is always turned on. 
    FOR EACH Customer NO-LOCK,
  EACH Order OF Customer NO-LOCK,
  EACH OrderLine NO-LOCK OF Order 
  BY Customer.Address :
  
DISPLAY
  Customer.NAME OrderLine.Price Order.Carrier.
END.
    		 Off No Using the BY clause on the top parent table's field that results in non-index sorting causes server-side join to be turned off. 
    FOR EACH Customer NO-LOCK,
  EACH Order OF Customer NO-LOCK,
  EACH OrderLine NO-LOCK OF Order
  BY Customer.NAME :

DISPLAY 
  Customer.NAME OrderLine.Price Order.Carrier.
END.
    		 On Yes Using the BY clause on the top parent table field that results in index sorting causes server-side join to be turned on. 
    FOR EACH Customer NO-LOCK,
  EACH Order OF Customer NO-LOCK,
  EACH OrderLine NO-LOCK OF Order
  BY  Order.Carrier :

DISPLAY
  Customer.NAME OrderLine.Price Order.Carrier.
END.
    		 Off No Using the BY clause on any middle table field that results in non-index sorting causes server-side join to be turned off.

Server-side join (-ssj) startup parameter

You may want to run some queries with server-side join enabled and disabled, to compare differences in performance. Server-side join can be disabled by using the Server-side join (-ssj) database server startup parameter, when you start the database up. You specify a value of "0" to disable it:

-ssj 0

Logging

To see if a particular query was done in the server, you can enable Query Information Logging for your client session. For example, start the client session using these startup parameters:

-clientlog logfilename
-logentrytypes QryInfo
-logginglevel 3

If the query was resolved in the database server, you see "Server-side join" in the log file entry. For example, for a FOR statement, you see:

Type: FOR Statement, Server-side join

For a dynamic query, you see:

Type: Dynamically Opened Query, Server-side join

Notes

  • If server-side join is enabled and the join query contains a variable in the WHERE clause, that variable should not be updated inside the FOR block, as changes to the variable's value are not communicated to the server after the join is initially sent by the client. If you do update a variable, then the results returned may not be as expected.

    The following example shows such a case:

    FOR EACH Customer NO-LOCK, 
  EACH Order OF Customer NO-LOCK
  WHERE order-date > myDateVar:
  ...
  myDateVar = new-value.
END.
    To avoid this behavior, separate the inner join into its own inner FOR block to have the client perform the join. For example:
    FOR EACH Customer NO-LOCK:
  FOR EACH Order OF Customer NO-LOCK
  ...
  • When a join query executes, there is no guarantee that any child table buffers of a join are released unless the record of its immediate parent table is fetched. With server-side join enabled, because the server only sends records that satisfies all levels of the join, you may see that a record that used to be released on a child table is no longer released. Therefore, if no records satisfy the query, then the child buffers are left in scope. For example: 
    FIND FIRST Order.

FOR EACH Customer WHERE Cust-num < 10 NO-LOCK, EACH Order of Customer NO-LOCK.
    If there are no Customer records that satisfy the WHERE clause, then the Order record remains in scope, whether server-side join is involved or not. If there are Customer and Order records that satisfy the query, then the Order record in scope is released. When server-side join is not enabled, the ABL client drives the resolution of the join so the client needs to retrieve a record from the parent level before trying to find records in its child table, in which case it does release the Order record. For this reason, it is not recommended that you depend on the availability of a record of a child table in a join after the join is executed.