473,544 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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","Da te<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","It emID<=" & 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 24439
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","Da te<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","It emID<=" & 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.amo unt) 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","Da te<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","It emID<=" & 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*******@yaho o.com" <ce*******@yaho o.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","Da te<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","It emID<=" & 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*******@SPAMh otmail.com
"It's not IT, it's IS"

<ce*******@yaho o.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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","Da te<=" & Date) 'For Dates

Cumulative: DSum("[Amount]","MyTable","It emID<=" & 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
5336
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 puzzling me a little: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five...
4
7281
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 for test and the other is production). The query is slow only in production. When running this query in both DB and looking at execution plan, statistics, etc, the onle difference is the...
4
6484
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 summarises the variances that each of my sales guys have created from their targets. This summarises by week, and works fine. I then re-use this into a cross-tab which presents a nice orderly...
3
4782
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 network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because...
1
2467
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
14351
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 date (OppForInvDate), Opportunity Description and Estimated Value of the opportunity (OppEstVal). All I want is to chart, cumulatively for each month, the totals for the month? Why is it so...
3
6883
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 cumulative percent failure of a certain product in my table. :: Cumulative Percent Failure Example: If you had 100 product shipped, and every month 10 units failed, you would have 10% failure the...
1
2464
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 column give me a cumulative sum of the no. of entries for . These cumulative sums will be grouped by {Planned_Close_Date] Here is what the CODE I have so far, it does not give me cumulated sums. ...
2
9824
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 design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view...
0
7420
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7602
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7764
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7368
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5291
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4911
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1837
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
983
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
657
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.