Re: Tips on domain aggregate replacements

Tech-Archive recommends: Fix windows errors by optimizing your registry

One other practical option is to use summary tables. A summary table is used in situations where the number of detail records is immense, but you just need a total. An example would be a banking application. When a cashier makes a deposit, the credit to the account is added to the table with the account detail and in the summary table(s) the following fields are updated - account balance, total deposits today, cashier total deposits (as examples). In this case the amount of effect to obtain the account balance is effectively spread across each individual transaction as opposed to being all at once in a query. This though will require creating code that updates all of the related tables if you're using Access. In more advanced DB's such as SQLServer, triggers can be setup saving a good amount of time.

Brian wrote: 
Thanks.

In the specific example that is plaguing me, it is a multi-user DB, but it is being run by all users locally on a terminal server, and the performance is the same whether one or all are using the app. The performance is suffering largely due to the complexity of certain reporting requirement (in addition to the speed of the server - the particular report takes about 1/4 as long to run locally on my development station, butthat is still about 1-3/4 minutes)

The biggest offender is a certain report that (in addition to a lot of other info) needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output.

The report layout, simplified, looks like this:

Item   Descr        Month1Total   Month2Total   Month3Total, etc. -> Month 12
  1       ABC              $1200           $1500             $1700
  2        DEF              $ 350            $    0              $   25
Totals at bottom

I calculate these like this:

First month's total -> DSum (blah, blah, blah where date is within next month
Second month's total -> DSum (blah, blah, blah where date is within the month after next (using dateadd to group into the correct months)

I'm trying to find an alternative, because I suspect the DSum as the bottleneck here.

"Klatuu" wrote:


Minimizing the use of domain aggregates is a laudable goal; however, one can never get rid of all of them. Whether you use a query or a DSum, DLookup, depends on a number of factors. If you just want the one value returned, I would suggest the DSum. If you have additional data you want at the same time, the query would be the way to go.
One key point, as I stated earlier, is to use the Execute method rather than the RunSQL. It is much faster. the Execute method only works for action queries. For select queries, I would suggest using a recordset. In fact, in my personal experience, it seems to be faster if I first create a recordset with no domain aggregates in it if I need a group of data. Then If I need to total a field, do a DSum on the recordset I created.

If you are having performance problems, there may be other factors to consider. Are you using a split database? Are there multiple users? Does each user have a copy of the front end on their own workstation? Users sharing a front end database or an unsplit database over a network is a real performance issue.


"Brian" wrote:


That's how I am already doing it (DSum, DLookup, etc.). However, everywhere I turn for help on speeding up my DB's, I hear, "Get rid of your domain aggregates", and that is why I asked the question in the first place.

Am I missing something here? Is there really some advantage to using Sum in a parameterized query over using DSum?

"Klatuu" wrote:


One note. Instead of the RunSQL, use the Execute method instead. It is much faster.
Now to do the sum of Quantity where Customer is 1234 and you want the results in ABC

First, make ABC a Variant data type, because in the DSUM function, it is possible to get a null return if there are no matches. Since I don't know where you are call it from, I will, for example purposes, assume you have a text box on a form named txtCustID, and the current record is Customer 1234

ABC = DSUM("[Quantity]", "MyCustomerTableName", "[CustomerID] = " & txtCustID
The above assumes the data type of [CustomerID] is a numeric field. If it is a text field, then use this version:
ABC = DSUM("[Quantity]", "MyCustomerTableName", "[CustomerID] = '" & txtCustID & "'"
If there are no records matching the criteria, then ABC will contain Null; otherwise, it will contain the sum for quantity for the selected customer.

"Brian" wrote:


Thanks, ALlan. Just a follow-up here, though, because I have always used a lot of queries & subqueries and am not very familiar with building recordsets in VBA.

As an example, let's say I want to assign the Sum of the Quantity field from the Invoices table where CustomerID=1234 to variable ABC. What I have been doing is writing a Sum query against Invoices, and then (in VBA) doing a DFirst (since the query will return only a single entry) or DLookup to grab the SubOfQuantity.

There are two parts I don't understand yet:

1. Can I just do this in VBA, without the query?
2. If not, how do I pass the Where clause to the query on the fly without using it in the DFirst or DLookup? Obviously, I cannot hard-code the criteria in the query.

"Allen Browne" wrote:


Brian, if you just want a single value in VBA, just go for it.

If you need multiple values, or values in a loop, you are probably better to build a recordset to get those values. It will be based on a totals query, or possibly a query with a subquery.

If subqueries are new, see:
   How to Create and Use Subqueries
at:
   http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian" <Brian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5022F742-E256-45EC-8DEA-2A0F6A5FA230@xxxxxxxxxxxxxxxx

Everything I am reading tells me I should minimize my use of domain aggregate
functions, so I am in the process of going through several apps to replace
these. In many cases, I can use a join in a query, but there are times when I
need to run the statement from VBA. I need a tip on how to accomplish this.

I know how to run action queries using RunSQL, but (how) can I run select
queries to return Max, Sum, etc. results to a variable or array in VBA? 



.

Quantcast