Re: Tips on domain aggregate replacements

Apart from what's been suggested, have you thought about an approach where by each person pulls data onto their own PC to run the report. The idea of replication also comes to mind, however while I'm quite familar with the concept, I haven't actually used it myself.

Brian wrote:
I will try moving the DSum's to the report. I already have several others there, and the quirk is that it seems to make navigating from page to page rather slow (of course, there could be some other reason for that).

This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. So, for all intents & purposes, there already is a copy of executable on each user's station. The BE sits in the same folder on that server where the FE sits. Would there be some advantage to having them each run their own copies locally on the terminal server?

It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night.

Having said all of this, I always split my databases. The only reason that I don't have the BE sitting on a server in their office with a FE on each workstation is that I have to get all my inefficiencies worked out first. Several of my other FE/BE db's seem to suffer from network latency issues, and I have not had time yet to go through the process of keeping the connection to the db open, trimming out domain aggregate functions, etc. to maximize efficiency.

I spend virtually all my time on user interface and data structure/integrity issues - don't have much time to rethink methodologies...

"Klatuu" wrote:


Okay, Brian, this is going to send you screaming to the looney bin. I went back to an app I did about 6 years ago, because I remember a specific report that took forever to run, and I made some mods that took it from about an hour to run to about 2 minutes. Here is the funny part. I took all the aggregate functions out of the query. Then in the report. I would do DSums from the controls on the report on the data returned by the query.

The other thing is you really need to split your database. Put the back end on the shared server and put a copy of the front end on each user's computer. This will do great things for you.

"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? 



.