472,342 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,342 software developers and data experts.

Cumulative Sum in query is very slow...

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
4 24117
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Tim Fountain | last post by:
We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is...
4
by: Yannick Turgeon | last post by:
Hello all, I've got a query which suddently became very slow. It now takes about 10 secs instead of 2 secs. I've got to identical DB (one is...
4
by: Matt Larkin | last post by:
I am pulling my hair out trying to solve this one (presumably because I am not particularly trained or skilled at access!) I have a query which...
3
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's...
1
by: Victor | last post by:
Hi There, I have a query witch gives me the following result: Period NumberOfItems 1 13 2 2 3 1 4 1 5 1
9
by: MikeSA | last post by:
Hi I a trying to create a chart that reflects monthly cumulative totals from a query. The query fields show sales opportunities forecasted invoice...
3
ChaseCox
by: ChaseCox | last post by:
Hi all, I have a problem that I have been looking at for a couple days now and I can not quite get it to work. I would like to calculate the...
1
by: wisemen | last post by:
I have a table with 2 columns, and .I want to run a query that will give me a cumulative sum of the no. of entries that have an <= and on the second...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.