By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,775 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,775 IT Pros & Developers. It's quick & easy.

Cumulative Sum in query is very slow...

P: n/a
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).

For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...

ID or Date Amount Cumulative
--------------- ------ ----------
a 2-3-2099 5 5
b 29-5-2099 3 8
c 12-8-2099 6 14
d 23-9-2099 2 16

My cumulative field uses,

Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's

This works well for relatively small recordsets, but for aggregate
'Group By' queries or large tables it is painfully slow.

Any ideas or comments?

TIA

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
ce*******@yahoo.com wrote:
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).

For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...

ID or Date Amount Cumulative
--------------- ------ ----------
a 2-3-2099 5 5
b 29-5-2099 3 8
c 12-8-2099 6 14
d 23-9-2099 2 16

My cumulative field uses,

Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's

This works well for relatively small recordsets, but for aggregate
'Group By' queries or large tables it is painfully slow.

Any ideas or comments?

TIA


The domain aggregate functions can be painfully slow.

You could try using a subquery to define your "cumulative" field instead
of using a function. Your query SQL might look something like this:

SELECT
mytable.mydate,
Sum(mytable.amount) AS SumOfamount,
(select sum(amount) from mytable as mytablecopy
where mydate<=mytable.mydate) AS cumulative
FROM mytable
GROUP BY mytable.mydate;

I think this should run reasonably quickly as long as your date field is
indexed.

Or, there are much faster replacements for Access' slow built in domain
aggregate functions which are available from
http://www.mvps.org/access/modules/mdl0012.htm

Nov 13 '05 #2

P: n/a
Br
ce*******@yahoo.com wrote:
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).

For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record.
Using "MyTable", the query results are...

ID or Date Amount Cumulative
--------------- ------ ----------
a 2-3-2099 5 5
b 29-5-2099 3 8
c 12-8-2099 6 14
d 23-9-2099 2 16

My cumulative field uses,

Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's

This works well for relatively small recordsets, but for aggregate
'Group By' queries or large tables it is painfully slow.

Any ideas or comments?


Dsum() is definately very slow especially for large data sets.

I'm sure there is a nice dynamic way to do it... but...

You can always dump the data into a temporary table and run some code
through it to do the summing. It can be remarkable quick this way.

Br@dley
Nov 13 '05 #3

P: n/a
As others have pointed out, domain funnctions are slow, but you have a more
fundamental problem. The time it takes to perform your sum is proportional to
n squared where n is the number of records. If you do it for 1000 records,
you've had to sum (I believe) 500500 records. That's why SQL doesn't do
running sums very well.

Workarounds:
1. Only do running sums on reports. Access will do a running sum for you
that's generated be keeping a running sum in memory as the rows are read from
the query.
2. Use a temporary result. Here, you first create a result table with an
autoincrement field, use an insert query to populate the table in order
(leaving the running sum blank), then use code to open a recordset on the
result table, loop through the rows using the autoincrement field as the sort
order, keep a running total, and write to the running total to the running sum
field.

On 20 Apr 2005 00:34:19 -0700, "ce*******@yahoo.com" <ce*******@yahoo.com>
wrote:
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).

For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...

ID or Date Amount Cumulative
--------------- ------ ----------
a 2-3-2099 5 5
b 29-5-2099 3 8
c 12-8-2099 6 14
d 23-9-2099 2 16

My cumulative field uses,

Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's

This works well for relatively small recordsets, but for aggregate
'Group By' queries or large tables it is painfully slow.

Any ideas or comments?

TIA


Nov 13 '05 #4

P: n/a
CEFrancke,
I dumped the idea of doing cumulative or running sums in queries and went
with building a table where I store my cumulative totals. Since it's stored
as a calculated result all a query has to do is retrieve the row--very fast.
I also index my totals table because I am more interested in faster results
in querying than I am in faster posting of results. Sounding like data
warehouse technique? Yup.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

<ce*******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).

For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...

ID or Date Amount Cumulative
--------------- ------ ----------
a 2-3-2099 5 5
b 29-5-2099 3 8
c 12-8-2099 6 14
d 23-9-2099 2 16

My cumulative field uses,

Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's

This works well for relatively small recordsets, but for aggregate
'Group By' queries or large tables it is painfully slow.

Any ideas or comments?

TIA

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.