OpenEdge 12: Great Performance Out-of-the-Box with Server-Side Join

OpenEdge 12.1 was released in September of 2019. Did you know that it extends the new server-side join (SSJ) support introduced in 12.0 to dynamic queries?

OpenEdge 12.0 introduced the SSJ functionality with the FOR statement. In OpenEdge 12.1, you can now use the SSJ functionality with FORWARD-ONLY, NO-LOCK dynamic queries. In this post, we share a tutorial to ease your ability to implement.

Optimizing the performance and scalability of an application were priorities in our delivery of OpenEdge 12. The database improvements in particular have been shown to significantly increase throughput—in some instances by 3x. The multi-threaded Database Server of OpenEdge 12 processes remote client requests concurrently, significantly improving database performance under load without requiring any application coding changes. To further enhance performance, OpenEdge 12 performs server-side joins with FOR-EACH queries that join multiple tables. These queries are now resolved in the database server rather than the client, and no coding changes are required. As a result, fewer records are typically returned to the client, leading to faster performance.

Server-side join supports up to 10 tables joined in the same logical database. Any query that cannot currently be resolved on the server side continues to operate as usual. With OpenEdge we furthered the capability by providing the ability to execute dynamic queries with joins on the server-side to improve performance of ABL queries.

Applications using an n-tier architecture connect to the database via network parameters. In this scenario, the client can be a GUI client, a TTY client or an agent process in PASOE. In previous versions (without SSJ), the resolution of a join would require the server to send records from the parent table to the client even if they were not a match in the join.

Ultimately, server-side join (SSJ) processing improves performance by resolving queries on the server and reducing the data sent over the network. SSJ is enabled by default, and is used to resolve joins that meet all the following conditions:

1. Joins in a client/server environment
2. Joins using NO-LOCK with the FOR statement or FORWARD-ONLY dynamic query
3. Joins with up to 10 tables on the same logical database

Let’s take a closer look at how this is implemented.

PROSERVE Parameters

The server-side join functionality is controlled by the -ssj database parameter.

By default, the database uses -ssj 1. The use of server-side join can be turned off by specifying -ssj 0 as a startup parameter when starting the database broker with PROSERVE.

The multi-threaded server (MTS) functionality should also be enabled for the server-side join to operate. MTS is also enabled by default.

You can use –ssj 0 if you want to turn off the functionality—for example, if you are analyzing the gains from using SSJ in your application.

Server-Side Join Processing with a FOR Statement

The following program shows a join using the FOR statement. The connection to the database is done using network parameters so that SSJ can be used.

Here are the steps to run the sample program using SSJ on a system with OpenEdge 12.1 installed:

1. prodb sports2020 sports2020
2. proserve sports2020 -S 20000
3. mpro sports2020 -S 20000 -p SSJ1.p

The connection to the database is done using the network parameter “-S 20000”. A client/server connection is established. The program SSJ1.p runs and displays the elapsed time for the query.

You can run the program on the same machine or on a remote machine as the database server.

When you run your program with SSJ, the performance is much faster. (In case you need to compare the performance with and without SSJ, the next section provides the steps you need to run without SSJ.)

You may ask: How fast is the performance? And you might simply answer, “very fast.”

Notes:

• Your mileage may vary. In actuality, the performance depends on the data and the reduction of data sent over the network.
• There are no changes to the index selection rules when you run the server-side join functionality.
• If needed, you can specify USE-INDEX to ensure that a certain index is used for optimal query resolution.

Testing Performance by Turning Off Server-Side Join

To turn off SSJ so that you can gain a performance comparison, follow these steps:

1. prodb sports2020 sports2020 -ssj 0
2. proserve sports2020 -S 20000
3. mpro sports2020 -S 20000 -p SSJ1.p

The difference with the steps in the previous section is that the “-ssj 0” option is used to disable SSJ.

When you test performance, you might want to shut down and restart the database broker so that improvements in the query execution are not associated with the caching of blocks or records.

The performance when you disable SSJ (-ssj 0) will be much slower.

Sample Code Using FORWARD-ONLY Dynamic Queries

Here are the steps to run the sample program using SSJ on a system with OpenEdge 12.1 installed:

1. prodb sports2020 sports2020
2. proserve sports2020 -S 20000
3. mpro sports2020 -S 20000 -p SSJ2.p

The connection to the database is done using the network parameter “-S 20000”. A client/server connection is established. The program SSJ2.p runs and displays the elapsed time for the query.

The sample program uses a FORWARD-ONLY dynamic query with NO-LOCK.

QryInfo Logging

You can analyze the execution of a query using the QryInfo logging option.

Follow these steps to run the sample program without SSJ with QryInfo logging on a machine with OpenEdge 12.1 installed:

1. proserve sports2020 -S 20000 -ssj 0
2. mpro sports2020 -S 20000 -p SSJ2.p \

                 -clientlog client.log -logentrytypes QryInfo -logginglevel 3

Follow these steps to run the sample program using SSJ with QryInfo logging on a machine with OpenEdge 12.1 installed:

1. proserve sports2020 -S 20000
2. mpro sports2020 -S 20000 -p SSJ2.p \

                 -clientlog client.log -logentrytypes QryInfo -logginglevel 3

In the screenshot, you can see the output of using QryInfo logging, with the sample program using a dynamic query. The left side shows the output when server-side join is disabled, and the right side shows the output with server-side join enabled (default).

Check out the introduction and demo videos here and get a closer and get a closer look:

An Introduction to Server-Side Join Processing with OpenEdge 12

More on OpenEdge 12.1 Extending Server-Side Joins to Dymanic Queries

Resources

For more information on server-side joins, check out these slides from a recent presentation by OpenEdge Development Fellow Richard Banville, “OpenEdge 12.0 Database Performance and Server-Side Joins,” or feel free to explore the Progress Information Hub links below:

• OpenEdge 12.0 Database Performance and Server-Side Joins
• Server-Side Join Processing for Dynamic Queries
• New Server-Side Join Processing 

Conclusion

The server-side join processing functionality in OpenEdge 12.x delivers great performance out-of-the-box.

Here are some of the things that we learned:

• SSJ is enabled by default.
• SSJ is available with the FOR statement and FORWARD-ONLY dynamic queries, NO-LOCK.
• Actual performance gains depend on the data being queried and the reduction of the records sent over the network.
• The QryInfo logging option can be used to analyze the query execution.

Have you seen the performance improvement of SSJ in your application?

How much performance have you gained? 2x, 3x? Please let us know in the comments.

Stay tuned. More enhancements to SSJ are coming. And if you don’t yet have OpenEdge 12.1 and want to test out the functionality for yourself, you can start a free trial today.

Start Your Trial Here

Thank you for reading.