Jim Dettman
jimdettman at verizon.net
Thu Aug 28 19:35:09 CDT 2014
wondering why the domain functions at all? Just use =sum(), etc. Jim Sent from my iPhone On Aug 28, 2014, at 8:14 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg> wrote: > Do you have an appropriate index on the field(s) being aggregated? I suspect not. > > On 28 Aug 2014 at 22:27, McGillivray, Don wrote: > >> 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 im! >> proved 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. >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com