If you run PickOrder.w and tab through all the filter fields without entering a value into one, you will see a small but noticeable delay before the Order browse displays the Orders. After all, the AVM has to read nearly 4,000 Order records from the database, plus their Customers and SalesReps, create a temp-table record for each of them, and buffer-copy the database records to the temp-table. Then it copies the ProDataSet definition and the entire contents of the ttOrder table to the window procedure. Considering the amount of work it is doing, it is pretty amazing that it does not take a lot longer than it does. However, if the number of rows were even larger, or if you were running the support procedure across an AppServer connection on a different machine, the delay would be much greater.
Generally, you should try to avoid giving your users the opportunity to browse through very large numbers of rows on the client, instead prompting them to filter the data in advance as the example window does. However, in some cases you need to move a potentially large number of rows from server to client, and it is often better to do it in batches so the user can see some of the rows before every database record has been read and copied into the temp-table and across to the client.
Note: When batching records, ProDataSet temp-table buffer queries on both the client and server must use a sort that is synchronized. Lacking this sort order agreement, results are not predictable. In addition, records being created on the client must be added to the end of the table.
This section extends the example from AdvancedEvents and Attributes to show you a way of batching records . At the same time we will show you how to limit the number of fields copied into the temp-table. After all, the window is only showing four fields from the ttOrder table, so there is really no point in copying every field into the temp-table, and more significantly, passing all those field values across to the client where they will never be seen or used.
To update the code:
1. To get started, copy the PickOrder.w procedure to PickOrderBatch.w, and the OrderSupport.p to OrderSupportBatch.p.
2. Change the RUN statement in the Main Block of PickOrderBatch.w to start OrderSupportBatch.p.
3. In the LEAVE trigger for cSalesRep in PickOrderBatch.w, change the RUN statement so that you run a different support procedure if the user does not enter a value into any of the filter fields, as shown:
/* There were selection criteria */
IF cSelection NE "" THEN
RUN fetchOrders IN hOrderProc (cSelection, OUTPUT DATASET dsOrder).
/* No selection so retrieve (the first) batch of rows, starting at the
first order. */
ELSE
RUN fetchOrderBatch IN hOrderProc
(0, "OrderNum,CustNum,SalesRep,OrderDate", OUTPUT DATASET dsOrder).
The new procedure fetchOrderBatch takes an Order Number to start with, and a list of fields to populate the temp-table. Since you want to start fresh when the user tabs out of the SalesRep, you just pass 0 as the starting point in the Order table. The four fields in the second parameter are the fields the browse uses; those are the only ones you need values for on the client.
The OUTPUT parameter is the same ProDataSet as before.
4. Switch over to OrderSupportBatch.p. First you need a new definition at the top, for example:
DEFINE VARIABLE cFieldList AS CHARACTER NO-UNDO.
The cFieldList is the list of fields to include in the ttOrder table, passed over to fetchOrderBatch.
5. Write the fetchOrderBatch procedure. It needs the three parameters you saw in the SalesRep trigger. For example:
PROCEDURE fetchOrderBatch:
DEFINE INPUT PARAMETER piLastOrder AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER pcFieldList AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR dsOrder BY-VALUE.
The selection that becomes the where-clause for the Order query needs to start with the first Order Number greater than the one passed in. For the call in the SalesRep trigger, this is the first Order in the database. In later calls, the INPUT parameter will be the highest Order Number retrieved so far.
The pcFieldList parameter passed in is saved in the variable cFieldList, which can be seen throughout the procedure. For example:
6. You use the BATCH-SIZE attribute on the ttOrder buffer to tell the AVM to fill only a maximum of 20 rows into the ttOrder temp-table at a time, as shown:
hDataSet:GET-BUFFER-HANDLE(1):BATCH-SIZE = 20.
7. The next four lines are the same as in fetchOrder, and you can copy them from there:
