Improving Client/Server data retrieval performance

Date:15 November 2001
Product/Release:Visual LANSA - RDML only
Abstract:Improve the performance for record retrieval when running Client/Server applications
Submitted By:LANSA Technical Support

When running client server style applications (ie. SuperServer applications), there is a time when processing the data locally is slower than performing the actions on the server. This is of course, dependent on the size and performance of the server.

Consider this very common example of adding records to a listview: 

SELECT FIELDS(…) FROM_FILE(FILEA) WITH_KEY(…)ADD_ENTRY #LTVW_1 ENDSELECT 

If there are only a few records, it is usually quicker to run the request on the client. When the number of records starts getting in to the many hundreds, the performance of the application will suffer as the OAM on the server returns the data in 50 record pieces due to the default block size, (see the CONNECT_FILE BIF in the LANSA BIFS guide). It will take quite a number of trips to the server to get all of the data. The most efficient way to read this data is to use a function running on the server, and then pass the data back in working lists. CALL_SERVER_FUNCTION allows the use of up to 10 working lists containing a maximum of 32,000 bytes per list to be returned in a single iteration. The following technique uses a single list and a *HEAYUSAGE function on the server to loop through the selected data on the server and return it to the client. 

Sample Client Code:

DEF_LIST NAME(#CHUNK) FIELDS(…) type(*WORKING) ENTRYS(9999)
* Pass "selection" criteria to the server EXCHANGE FIELDS(#A #B #C)
* Run server function until no data left to retrieve
* Operation of SELECT means read data from file
* Server returns MORE if more data to return, and END if no data left
CHANGE FIELD(#OPERATION) To(SELECT)
DOWHILE COND('#OPERATION *NE END')
EXCHANGE FIELDS(#OPERATION)

* Clear working list, and pass to server
CLR_LISR NAMED(#CHUNK)

* Run server function
USE BUILTIIN(CALL_SERVER_FUNCTION) With_Args(*SSERVER_SSN SERFUN Y Y #CHUNK) To_Get(#IO$STS)

* Check here that Server function ran OK
* Moved returned items into grid, ignoring duplicates
SELECTLIST NAMED(#CHUNK)
ADD_ENTRY TO(#LTVW_1)
ENDSELECT
ENDWHILE

Sample Server Code:

FUNCTION OPTIONS(*HEAVYUSAGE *DIRECT) RCV_LIST(#CHUNK)
DEFINE FIELD(#CUR_ENTRY) REFFLD(#LISTCOUNT)
DEFINE FIELD(#ALL_COUNT) REFFLD(#LISTCOUNT)
DEFINE FIELD(#CHUNK_CNT) REFFLD(#LISTCOUNT)
DEFINE FIELD(#NEXT_STOP) REFFLD(#LISTCOUNT)

* Chunk size is calculated as
* 32000 / number of bytes per list entry
* e.g. A list with a length of 100 bytes can return 320
* entries per iteration
DEFINE FIELD(#CHUNK_SIZE) REFFLD(#LISTCOUNT) DEFAULT(???)

* Current operation
* SELECT - Read records from file
* MORE - Return 120 records to the VL form
* CLOSE - Close the FILE and then end
* END - No more data to Send
DEFINE FIELD(#OPERATION) REFFLD(#STD_OBJ)
DEF_LIST NAME(#ALL_ITEMS) FIELDS(…) COUNTER(#ALL_COUNT) TYPE(*WORKING) ENTRYS(0009999)
DEF_LIST NAME(#CHUNK) FIELDS(…) COUNTER(#CHUNK_CNT) TYPE(*WORKING) ENTRYS(0009999) *
Case of_FIELD(#OPERATION)

* When being asked to build the list
WHEN VALUE_IS('= SELECT')

* Ensure everything reset each time as the function is heavy usage
CLR_LIST NAMED(#CHUNK)
CLR_LIST NAMED(#ALL_ITEMS)
CHANGE FIELD(#CUR_ENTRY) TO(*ZERO)
CHANGE FIELD(#NEXT_STOP) TO(*ZERO)

* Generate list of required data
SELECT FIELDs(#All_items) FROM_FILE(…) WITH_KEY(…) 
ADD_ENTRY TO_LIST(#ALL_ITEMS)
ENDSELECT

* At least one record returned
IF COND('#ALL_COUNT *GT *ZERO')
EXECUTE SUBROUTINE(GET_MORE)
ELSE
CHANGE FIELD(#OPERATION) TO(CLOSE)
ENDIF

* When asked to get the next chunk of records
WHEN VALUE_IS('= MORE')
EXECUTE SUBROUTINE(GET_MORE)
WHEN VALUE_IS('= CLOSE')
CLOSE FILE(…) CHANGE FIELD(#OPERATION) TO(END)
ENDCASE
EXCHANGE FIELDS(#OPERATION)
RETURN

* Get the next chunk of records from the main list
SUBROUTINE NAME(GET_MORE)
CLR_LIST NAMED(#CHUNK)
CHANGE FIELD(#CUR_ENTRY) TO('#NEXT_STOP + 1')

* Set next stop point for passing records
CHANGE FIELD(#NEXT_STOP) TO('#NEXT_STOP + #CHNK_SIZE')
CHANGE FIELD(#OPERATION) TO(MORE)

* Loop through entries and build chunk of data
* to pass to the client
BEGIN_LOOP USING(#CUR_ENTRY) FROM(#CUR_ENTRY) TO(#NEXT_STOP)
ADD_ENTRY TO_LIST(#CHUNK)
LEAVE IF('#CUR_ENTRY = #ALL_COUNT')
END_LOOP

* If the current entry is the last
IF COND('#CUR_ENTRY = #ALL_COUNT')
CHANGE FIELD(#OPERATION) TO(CLOSE)
ENDIF
ENDROUTINE

Each time the server function is called, it returns the operation flag to tell the client whether it has more data available. Once the last record has been sent, the flag is set to CLOSE. The client will then drop out of it's processing loop.  The above code could be expanded to pass up to the maximum 10 lists allowing up to 320,000 bytes of data and/or 99,990 entries to be returned to the server per iteration. This is a significant amount of data and should suffice for most applications. 

Note: The online documentation for the CALL_SERVER_FUNCTION Built-in Function states the following:

Working Lists 1 through 10 to be passed to the function on the server. The aggregate byte length of a working list passed to a server cannot exceed 32,000 bytes. The aggregate byte length is the entry byte length multiplied by the current number of entries. This is often mis-interpreted as 32,000 bytes being the total number of bytes that can be passed to the Server, whereas in fact the total is 320,000 bytes ie. 10 working lists of 32,000 bytes each.