Client-server strategy for simulating a server-side join is

Posted by dbeavon on 25-Oct-2018 12:29

Connecting ABL code to a remote OE database (via "client-server") can be quite a lot slower than running code in "shared memory".  There are a variety of kb articles that provide suggestions and workarounds for this.

One of the bigger issues with client-server is that nested loops are very expensive.  The best optimization available for gathering data quickly is to use FOR EACH NOLOCK loops that aren't nested (ie they are independent).

Today I tried using FOR EACH NOLOCK loops to gather orders in a date-range, and join them to the related customers for the data in that date range.  The idea was to do this in independent loops since this is the only way to get the data quickly.  Server-side joins are not yet available for ABL code connecting against the OE database (although they are oddly available in the dataserver products).

The first step is to start the sports database so that it sends traffic over the network as efficiently as possible

proserve -S 3333 -minport 3400 -maxport 3500  -n 150 -Mn 15 -Mi 1 -Ma 100 -Mpb 6 -prefetchNumRecs 1000  -prefetchFactor 100 -prefetchDelay -Mm 32000  sports2000

In PDSOE I proceed set up the database connection and create a new project.  In order for this particular program to work well, you need to introduce this to your PDSOE startup parameters: -inp 100000 -tok 10000

Now I am ready to get the data as quickly as the database and network will allow.  The following code is intended to use only two round-trips.  The first gets all the orders in a date range, and sends them back from the database in a batch.  And the second round trip to the database will retrieve all the related customers, as identified by their customer numbers.

Here is the code.  You will be prompted to push the spacebar between the round-trips.

USING Progress.Lang.*.

BLOCK-LEVEL ON ERROR UNDO, THROW.
  
/* ********************************************************************* */
/* Local data                                                            */
/* ********************************************************************* */
DEFINE TEMP-TABLE TT_Order NO-UNDO 

   FIELD OrderNum AS INTEGER 
   FIELD OrderDate AS DATE 
   FIELD OrderStatus AS CHARACTER
   FIELD CustNum AS INTEGER  
   
   INDEX TT_Order1 IS UNIQUE PRIMARY OrderNum
      INDEX TT_Order2 CustNum.
   
/* ********************************************************************* */
/* Local data                                                            */
/* ********************************************************************* */
DEFINE TEMP-TABLE TT_Customer NO-UNDO 

   FIELD CustNum AS INTEGER  
   
   FIELD CustName AS CHARACTER INIT ? 
   
   INDEX TT_Customer1 IS UNIQUE PRIMARY CustNum.




/* ************************************************************************ */
/* Do the following in a do/catch                                           */
/* ************************************************************************ */
DO ON ERROR UNDO, THROW:
                        /*                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   */

   /* Monitor network traffic from this point onwards */            
   MESSAGE "Starting {&FILE-NAME} {&LINE-NUMBER}".
   PAUSE. 
   
                      
   /* ************************************************************************ */                         
   /* Get orders represending only a hundred customers or so                   */                  
   /* ************************************************************************ */          
   FOR EACH Order 
      WHERE Order.OrderDate >= 01/01/1998
      AND   Order.OrderDate < 02/01/2998
      NO-LOCK :
         
      CREATE TT_Order.
      TT_Order.OrderNum = Order.OrderNum.
      TT_Order.OrderDate = Order.OrderDate.
      TT_Order.OrderStatus = Order.OrderStatus.
      TT_Order.CustNum = Order.CustNum.
      
      FIND FIRST  TT_Customer
         WHERE    TT_Customer.CustNum = TT_Order.CustNum
         NO-LOCK 
         NO-ERROR.
      
      IF NOT AVAILABLE TT_Customer THEN 
      DO:
         CREATE TT_Customer.
         TT_Customer.CustNum = TT_Order.CustNum.
      END.
      
   END.
   
                         
                          
   /* ********************************************************************* */
   /* Build query against relevant customers (simulate server-side join)    */
   /* ********************************************************************* */
   DEFINE VARIABLE v_FilterQuery  AS CHARACTER NO-UNDO.
   DEFINE VARIABLE v_FilterHandle AS HANDLE  NO-UNDO.
   DEFINE VARIABLE v_CountCustomers AS INTEGER NO-UNDO.
   DEFINE VARIABLE v_First AS LOGICAL NO-UNDO INIT TRUE.
   v_FilterQuery = "FOR EACH Customer WHERE ". 
                         
   FOR EACH TT_Customer NO-LOCK :
                  
      v_CountCustomers  = v_CountCustomers  + 1.
      IF NOT v_First THEN v_FilterQuery = v_FilterQuery + " OR ". 
      v_FilterQuery = v_FilterQuery + " ( Customer.CustNum = " + STRING(TT_Customer.CustNum) + " ) ".
      v_First = FALSE .
      
      /* Avoid issues with -inp and -tok */      
      IF v_CountCustomers  > 100 THEN LEAVE. 
   
   END.
   
   v_FilterQuery = v_FilterQuery + " USE-INDEX CustNum NO-LOCK: ".
                     
     
   /* ****************************************************************** */
   /* Build database query for OE                                        */
   /* ****************************************************************** */
   CREATE QUERY v_FilterHandle.
   v_FilterHandle:SET-BUFFERS(BUFFER Customer:HANDLE).
   
              /*                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   */
   /* Doing the query work */            
   MESSAGE "About to prepare query at {&FILE-NAME} {&LINE-NUMBER}".
   PAUSE. 
   
   /* Strategy falls apart */
   v_FilterHandle:QUERY-PREPARE(v_FilterQuery).
     
   /* Doing the query work */            
   MESSAGE "Everything else after that is fine {&FILE-NAME} {&LINE-NUMBER}".
   PAUSE. 
   
   v_FilterHandle:QUERY-OPEN().


   /* ********************************************************************* */
   /* Loop the data                                                         */
   /* ********************************************************************* */
   REPEAT :
      
      /* Loop */
      v_FilterHandle:GET-NEXT().
      IF v_FilterHandle:QUERY-OFF-END THEN LEAVE.
      
      FIND TT_Customer WHERE TT_Customer.CustNum = Customer.CustNum EXCLUSIVE-LOCK.
      TT_Customer.CustName = Customer.Name.
      
                                         
   END. /* REPEAT */
         
   /* ****************************************************************** */
   /* Close/delete query                                                 */
   /* ****************************************************************** */
   v_FilterHandle:QUERY-CLOSE().
   DELETE OBJECT v_FilterHandle.
   
              
   /* Done */            
   MESSAGE "Done at {&FILE-NAME} {&LINE-NUMBER}".
   PAUSE.     
      
      
      
   /* ********************************************************************* */
   /* Shouldn't be errors                                                   */
   /* ********************************************************************* */
   CATCH v_ProError AS Progress.Lang.ProError:
      
      MESSAGE "HERE {&FILE-NAME} {&LINE-NUMBER} ERROR".
      PAUSE. 
      
   END CATCH.


END.  
   
   
 
    
 

      

Hope this is clear.  Everything works pretty much as expected.  The only exception is the QUERY-PREPARE statement which works so badly that it pretty much defeats the purpose of my server-side join simulation.

Posted by dbeavon on 12-Nov-2018 14:26

There is now an article that describes the problem with QUERY-PREPARE in a client-server environment (even where the entire database schema is cached locally:

knowledgebase.progress.com/.../QUERY-PREPARE-generates-excessive-network-traffic

Unfortunately the QUERY-PREPARE does an excessive amount of work over the network.  This work - which is done while preparing the query - ends up costing more than the work of actually gathering the customer data.  It does not seem that QUERY-PREPARE would need the remote schema if it is already cached locally.  IE. The QUERY-PREPARE shouldn't need to get the remote schema one time, let alone doing it repeatedly for every single predicate  ( Customer.CustNum = 1234).

All Replies

Posted by dbeavon on 25-Oct-2018 12:49

Just to be clear, the simulation of a server-side join that I provided above is intended to produce the same results as the program below.  I would obviously prefer to write my code this way, but server-side joins are not yet available for ABL code connecting against the OE database.

 

       
   FOR EACH Order 
      WHERE Order.OrderDate >= 01/01/1998
      AND   Order.OrderDate < 02/01/2998
      NO-LOCK ,
      EACH Customer WHERE Customer.CustNum = Order.CustNum
      NO-LOCK:
          
      CREATE TT_Order.
      TT_Order.OrderNum = Order.OrderNum.
      TT_Order.OrderDate = Order.OrderDate.
      TT_Order.OrderStatus = Order.OrderStatus.
      TT_Order.CustNum = Order.CustNum.
       
      FIND FIRST  TT_Customer
         WHERE    TT_Customer.CustNum = TT_Order.CustNum
         NO-LOCK
         NO-ERROR.
       
      IF NOT AVAILABLE TT_Customer THEN
      DO:
         CREATE TT_Customer.
         TT_Customer.CustNum = TT_Order.CustNum.
         TT_Customer.CustName = Customer.Name.
         
      END.
       
   END.

If you monitor the network activity you will see that my simulation results in far less packets being transferred across the network.  And control of program-flow is only transferred back and forth twice between the client and server, rather than thousands of times.

Even so, the QUERY-PREPARE statement works so badly that I don't think I would use my server-side join strategy very often as a substitute for a nested FOR EACH.  If you watch the network activity for the QUERY-PREPARE, you will see that for every CustNum in the WHERE clause, the client code has to query the database server for schema.  This results in one round trip for every CustNum, even though the same schema keeps being sent back over and over.

Am I missing something?  Shouldn't a program cache database schema, at least for the lifetime of QUERY-PREPARE?  Would the performance improve if I saved the schema cache locally and started the app with 

-cache?

Posted by Patrick Tingen on 25-Oct-2018 13:46

Perhaps I am missing something, but why do you still fetch the customers from the server inside your loop? In the setup part of the program (line 57-66) you already get all data for the customer table from the server in individual non-nested loops. Cool. But then why not run the query against tt_customer?

Posted by dbeavon on 25-Oct-2018 14:53

Patrick,  the second half of the program, after line 70 is the interesting part.  It performs a simulation "server-side join" against the customers.  It allows me to pull in any customer data that I may need (other than the CustNum which was found on the order).

>> Why not run the query against TT_Customer?

That is essentially what I'm doing.  I'm generating a list of the required customers and querying them from the database in a single round-trip (by building a non-nested FOR EACH loop based on the CustNum).  If I use the TT and do nested FOR EACH loops (or FOR EACH & FIND) then it would involve a hundred individual round-trips.

Hope this is clear.  

FYI I tested the "-cache" to make the entire schema available to the client session.  It does NOT seem to improve the behavior of "QUERY-PREPARE".  The behavior of that "query-prepare" operation is quite disappointing.   It repeatedly queries the same schema for the same table column (a hundred times, proportional to the number of customers in the WHERE clause).

Posted by Patrick Tingen on 26-Oct-2018 01:13

It pretty much depends on your data distribution, but in a test where I played with this, I found that is was faster to just fetch all orders and all customers first in separate loops and then perform the query locally:

/* Local data
*/
DEFINE TEMP-TABLE TT_Order NO-UNDO
 FIELD OrderNum    AS INTEGER
 FIELD OrderDate   AS DATE
 FIELD OrderStatus AS CHARACTER
 FIELD CustNum     AS INTEGER 
 INDEX TT_Order1 IS UNIQUE PRIMARY OrderNum
 INDEX TT_Order2 CustNum.
    
DEFINE TEMP-TABLE TT_Customer NO-UNDO
 FIELD CustNum  AS INTEGER 
 FIELD Name AS CHARACTER INIT ? 
 INDEX TT_Customer1 IS UNIQUE PRIMARY CustNum.
 
/* Get data
*/
FOR EACH Order NO-LOCK
  WHERE Order.OrderDate >= 01/01/1998
    AND Order.OrderDate < 02/01/2998:

  CREATE TT_Order.
  BUFFER-COPY order TO TT_Order.
END. 

FOR EACH customer NO-LOCK:
  CREATE TT_Customer.
  BUFFER-COPY customer TO TT_Customer.
END.

/* Join
*/
FOR EACH TT_Order, 
  EACH TT_Customer OF TT_Order:

  DISPLAY 
    TT_Order.ordernum
    TT_Customer.name
    .
END. 

In this case, you get all customers which is probably too much, but like I said, it depends on your situation. At one client we used this to speed up some processing. 

Posted by Patrick Tingen on 26-Oct-2018 01:15

In addition, to further limit network traffic, I should have used the FIELDS phrase on the FOR-EACH statements.

Posted by dbeavon on 26-Oct-2018 08:20

Patrick, thanks for your tip about getting all customers.  I'm always happy to hear from other OE customers who are using ABL over a client-server connection.  I'd guess that most OE developers are still using "shared memory", and it is hard for them to appreciate the additional challenges involved in running ABL on a remote tier, like from load-balanced PASOE servers.

Improving the performance of client-server joins is not an new topic;  here are some links to various explanations that explain why client-server behaves poorly compared to a similar "shared memory" program:

knowledgebase.progress.com/.../000053086

knowledgebase.progress.com/.../18342

knowledgebase.progress.com/.../000012195

community.progress.com/.../add_server-side_joins__better_networking_performance

I would certainly get *all* table records whenever dealing with moderately sized tables, but in our environment the customers list and products list grow very large.  

I would rather get the orders in a date range (or the active ones) and then go after the specific customers and products in a secondary step.   When getting customers and products, it is likely that I would only need less than 1,000 of them (as identified by the foreign keys on the orders), and this would be under 1% of the total customers/products in the database.

That is why I was playing with QUERY-PREPARE as a workaround, but the strategy wasn't very successful.  The purpose of using the dynamic query was self-defeating since the *compilation* of the query did all the same round-trips that I was hoping to avoid in the first place!  Personally I think it is a bug that the compilation of my dynamic query would need to repeatedly fetch the "CustNum" schema hundreds of times in a loop.  This seems to be the case even if you use -cache.  The compilation ends up taking at least 30 ms by itself, and that is before we even begin to fetch the customers.

I will open a support case.  I think the behavior of QUERY-PREPARE needs some explanation, in the very least.  

Posted by Lieven De Foor on 30-Oct-2018 10:22

In addition, could you try replacing the REPEAT loop with a DO WHILE(TRUE) loop?

Repeat has a noticeable overhead, so I'm curious if that could also have some impact on performance here...  

Posted by dbeavon on 12-Nov-2018 14:26

There is now an article that describes the problem with QUERY-PREPARE in a client-server environment (even where the entire database schema is cached locally:

knowledgebase.progress.com/.../QUERY-PREPARE-generates-excessive-network-traffic

Unfortunately the QUERY-PREPARE does an excessive amount of work over the network.  This work - which is done while preparing the query - ends up costing more than the work of actually gathering the customer data.  It does not seem that QUERY-PREPARE would need the remote schema if it is already cached locally.  IE. The QUERY-PREPARE shouldn't need to get the remote schema one time, let alone doing it repeatedly for every single predicate  ( Customer.CustNum = 1234).

This thread is closed