D
dbeavon
Guest
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 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.
Continue reading...
Continue reading...