[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