McGillivray, Don
DMcGillivray at ctc.ca.gov
Thu Aug 28 17:27:55 CDT 2014
Hi Listers, I have a form in an Access application (users have the Access 2010 runtime only, on Win 7; Split FE/BE, with BE on network share) where I need to display and continuously evaluate the cumulative count and value of items and assign an incrementing serial number to child records as they are entered. At this point, the form uses domain aggregate functions to determine the count and sum of the transactions, and the max plus 1 for the next serial number. At modest volumes (<=200 records) performance is acceptable. Beyond that, performance becomes increasingly degraded, to the point of being almost unusable by the time 400 records have been entered. Obviously, the domain aggregate approach is not working for this situation, so I'm looking for more efficient approaches to fulfill the aggregating/incrementing requirements. Can anybody point me in a better direction? Thanks! Don McGillivray --------------------------------------------------------------------------- For those interested in further detail, please read on . . . The application records customer payment data. Ultimately, the payment data collected by the system will be imported into an Oracle database. The payments, each consisting of a header record and one or more GL distribution records, are entered in batches that correspond to daily bank deposits. The payment header record contains the payment date, check number, and amount, and the GL distribution record contains line item detail including an application number and the GL account to which the revenue is booked. Data entry begins with the creation of a batch (deposit) header record that includes the date, number of payments, number of applications paid for, and total deposit amount. (The item count and amount are used as "target" values during data entry to ensure that everything balances in the end.) Once the batch header record is complete, the payment records are entered one at a time, along with their GL details. So I have a simple hierarchy of data entities: Deposit Header - Payment Header - Payment Details, and the entry form is designed (main form, sub-form, sub-form) to accommodate this structure. That's the quick and dirty description of the basic setup. A further wrinkle is that each payment record (child of the top level) is associated to one or more stamp numbers that are affixed to the check upon receipt, and those numbers must be included in the distribution records (grandchildren to the top level) for each payment. The stamp numbers are composed of a date string (YYMMDD) followed by an incrementing serial number from 1 to n each day, according to the number of applications being paid for. So, based on the application count of the batch header, a list of stamp numbers can be generated for each batch, and, assuming the checks are entered in stamp number order, the next available number may be assigned automatically to each payment until all are used up. The entry form allows the user to select from a combo box any valid stamp number for any payment if necessary, but the typical scenario is to allow the system to assign the numbers in sequence to the distribution records automatically. To help ensure accurate data entry, the user is required to have entered values that agree with the target values. For example, on a given payment, before a new payment may be entered, the value of the distribution records must equal the check amount as entered in the payment header. Similarly, the batch itself may not be posted until the sum of its payment records equals the previously entered batch totals (item count and amount.) These rules are enforced by the form by comparing the batch and payment header entries to the sums of the line items as data is entered. The application has been in production since May, and has functioned well as designed. However, this week an unusually large volume of payments (>600) arrived on a single day, and as the batch entry progressed beyond about 300 records, the form's performance became increasingly degraded, until it was essentially unusable. The degradation is probably due to the methods I've employed to determine the next available stamp number, compounded by the tabulation of the item count and cumulative total for the batch as data entry progresses. In both cases, I'm using domain aggregate functions to return the count and sum of payments entered, along with the max plus 1 for the next available stamp number. As the number of records in the batch grows, these calculations take longer and longer. The effect was observed during development, leading me to modify the table structure so that new records land in temporary tables before being posted by the user to the permanent ones. This improved things quite a bit by avoiding the impact of aggregating over a table whose content continues to grow over time, but apparently that move is not enough when the batch volume rises beyond a certain point.