Add server-side joins / better networking performance - OpenEdge Database - Products Enhancements - Progress Community

 OpenEdge Database

What new features or enhancements could improve the OpenEdge Database? Use this challenge to let the OpenEdge team know what would make your life easier by reducing costs and increasing productivity!

Add server-side joins / better networking performance

I have been monitoring the network traffic between a client and the server. I noticed that even a simple query generates a lot of traffic. An example against the sports2000 database (using OpenEdge 11.2, Windows platform, -Mm 8192):

FOR EACH customer NO-LOCK, EACH order OF customer NO-LOCK.

This kind of query generated 1.3 MB of transferred data in about 4700 network packets.

What is hard to understand is what happens when I delete all orders from the database and run the same query. The result:

1 MB of data in 4690 packets.

Because there were no orders, the query returned no rows at all. Still, 1 MB of data was transferred. I saw (viewing the packet contents) that all of the customer data was transferred to the client! Apparently in this kind of query, the whole customer table is transferred to the client and then on the client side each customer is handled one by one, fetching the orders for each customer. Does not seem very effective.

I then changed the query to use PRESELECT instead of FOR. Things got a little bit better:

850 KB of data in 4500 packets.

Monitoring the packet contents, I saw that now there was no customer data transferred. Still the amount of data was quite high. I assume that all the customer table rowids (or index data) were transferred to the client. In an ideal world, there would be only a couple of packets generated in this sample case.

Of course, we have different kinds of networks and configurations, so maybe this problem is not so bad in every case. And using AppServer (with shared memory connection) certainly helps in some cases, but it's like relieving the symptoms, not curing the disease. And sometimes AppServer can't use the shared memory connection to the database; then it becomes just another remote client with the same networking problems. 

Here's one KB article I found on this matter: http://knowledgebase.progress.com/articles/Article/18342

OpenEdge database is fun to work with, reliable and performs quite nicely when running queries against one table, but joins are another story. Please, Progress, implement server-side joins.

Status update

We are working on identifying how best to provide server-side query resolution for the ABL where it is appropriate.  In some instances it does not make sense to add a layer of query resolution.  This is being researched and planned for a future release.

Comments
  • Here's another KB article which confirms the lack of server-side joins:

    knowledgebase.progress.com/.../000012195

    Quote:

    “PROGRESS translates joins into multiple single-table queries, which are the only types of queries the server can execute today. For example, the query

    for each customer, each order of customer:

    is executed by the client by opening to queries: one the customer and one for order. After each customer record is received, the client opens the second query for orders where order.cust-num = customer.cust-num, and retrieves all the orders for the customer. When it is done with the orders it goes to the next customer, and so on.”

  • Absolutely true to much network traffic.

    But I think every code you write today should be appserver based with usage of datasets.

    In smaller to medium environment your appserver can be on the database server which gives you a mega boost on performance. You loose the network traffic and have the self service performance.

    I agree with you that the joins maybe can be optimized. But using appserver decreases the importance of this topic a lot.

    I guess you already use the -prefetchdelay parameters added in 10.2B06 to tune your network traffic a bit better.

  • Yes, -prefetchdelay is used, it helps a little. I agree that using AppServer is good practice, but I see three problems here:

    - using AppServer makes programming/configuration more complex and thus reduces productivity

    - AppServer-enabling an existing application can be a massive effort (we have a lot of code that dates back to pre-AppServer era)

    - large environments might not be able to use AppServer and Database on the same machine

  • Marko, well made comments. I totally feel the pain from the field. My daily job is running Application modernisation assessements. So I know the reality.

    But said that I have to disagree an a few remarks

    - using AppServer makes programming/configuration more complex and thus reduces productivity

    Disagree, with the right skills and toolset you even get better productivity. For sure in the beginning you loose some time to setup the structure but very quickly you win back time because of a better layered architecture. Not mentioning the other possibilities you gain. Use Mobile, Bpm, Other UI without touching the backend code.

    - AppServer-enabling an existing application can be a massive effort (we have a lot of code that dates back to pre-AppServer era)

    Yes this can be a hell of a job. And most OE applications are from before 1998 when appserver was launched.

    But progress has a good "Application Modernisation Assessement" which figures out a phased approach, low risk path to a newer architecture.

    - large environments might not be able to use AppServer and Database on the same machine

    Absolutly true we can loose some performance here. But today DB and appserver machine can be connected with fiber connect stuff (sorry I'm not a network guru) which resolves in a very low latency.

    200 users easily run on a single machine. And you also have the possibility to put your gold customers/users on the self service connection and others on the remote.

    But I totally agree if the joins can be improved it should happen :)