How to replace DSUMs killing network performance?

[Tom]

<Repeating my post to the Access Multiuser group in the hopes of
eliciting a few more responses to this issue>

All,

I recently split my first database application into a front end/back
end server application for distributed users so I could get out of
data entry. All appears to be working as intended with one very major
exception: any queries containing DSUMs (or reports built on those
queries) are running painfully slow. I'm fairly certain the DSUMs are
the culprit after evaluating performance comparisons between queries
with DSUMs and queries without them over the network.

I generally have no more than five users connected simultaneously, and
all traffic is confined to a corporate intranet for the time being.
The long term plan is to migrate the application to an Oracle app with
an enterprise web front end, but that's at least a year away and I
need the app running in its current state until then.

What's the best path forward to replace all the DSUMs with something
faster in a multi-user, server-based environment? As an alternative,
is there a simple way to simply copy the data from the back end over
to the users' front ends and continue using the DSUMs as-is?

Thanks for your advice,

Tom

 

[Allen Browne]

Yes, the domain aggregate functions can do that.

Subqueries are one alternative:
http://allenbrowne.com/subquery-01.html
But if the query is headed for a report, that could give you a "multi-level
group-by" error.

Might it be possible to create another query that performs the aggregation,
and JOIN that (as an input "table") in your exising query? This could give
you the total per row without the performance issue of the DSum() or the
grouping error of the subquery.

If there are problems with applying the criteria you need for the lower
level query, it might be possible to write the SQL property of that QueryDef
before you OpenReport. That's one way to dynamically supply criteria to the
lower level query.

It should not be necessary to copy all the data over to the front end.
However, it might be worth creating a table in the front end to hold the
aggregated values. Before you open report, you clear it out (DELETE FROM
MyTempTable, and populate it with the totals (execute an append query
statement.) You can then JOIN this temp table to your existing one.

Others may have more ideas.