McGillivray, Don
DMcGillivray at ctc.ca.gov
Fri Aug 29 14:14:22 CDT 2014
Hi Jim,
Your description squares pretty well with my understanding of the appropriate uses for domain aggregates. Upon closer examination, I discovered that I am using an "in([expression])" clause in the criterion argument of one of my domain aggregate functions, and suspect that that may be having an impact as well. Basically, I'm seeking the max of the values in a column over a subset of grandchild (GL distribution) records related to a set of records (Payments) that are children to the top level (Deposit) record. So the call to the function looks something like:
lngMaxUsed = DMax("[SerialNum]","tblGrandChildren","[ParentID] in(SELECT ID from tblParent as par WHERE par.ParentID = " & GrandParentForm!txtID & ")")
Those are not the actual table, column, and control names, but I think you get the idea. I suspect that the embedded query in the criterion is adding time to the process. I'm considering adding a column to the grandchild table and populating it with the grandparent ID as the grandchild records are added. Then the call to DMax() can simply refer to that column instead of having to run the SQL to retrieve the subset of parent records.
Would you concur that the "in([expression])" clause may be the culprit - or at least exacerbating the problem?
Thank you for your response!
Don
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, August 29, 2014 6:03 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Alternatives to domain aggregate functions
Don,
Just to add a bit to my post from last night as I couldn't see this original post, you've got to be careful where you use Domain functions.
What they do is just wrap up a SQL Statement so you can execute SQL in
places where you ordinarily can not. For example, Dlookup() is "SELECT <x>
FROM <y> WHERE <Z>". So any place you can use SQL directly you should and
a domain function is not appropriate.
For example, totaling a field in a sub form. Just place a control in the
footer and set its control source to: =Sum(<expression>)
One place where domain functions are really a no-no is inside of a query.
They are totally un-optimizable by the query processor and you are guaranteeing yourself poor performance for anything over a couple of hundred records.
Alternatives are executing SQL in code, performing operations on a record
set directly, etc.
Domain functions are handy to use, but in general, you'll find that you don't use them often because there are other ways to do things.
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Thursday, August 28, 2014 06:28 PM
To: accessD at databaseadvisors.com
Subject: [AccessD] Alternatives to domain aggregate functions
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