How can I tune a query that does a cumulative sum?

[Darryl]

Hi all, I've written a query that calculates the cumulative sum of a column.
I've used the DSum function as I need to use this query for updates. It
works, but it's way too slow as I have >60,000 rows to process (Background:
This was originally done in Excel, but I need to do it in Access now because
of Excel's 65K row limit). Does anyone know how I might be able to optimize
this? I've heard that using in-line functions is the fastest, but I'm not
sure how to even go about doing this. Will someone be able to help?

The query I've written is as follows:
SELECT t1.ID, t1.Customer, t1.Customer_Lifetime_Value,
DSum("Customer_Lifetime_Value","06_Lifetime_Value_Tier","[ID] <= " & [ID]) AS
Cumulative_Lifetime_Value
FROM 06_Lifetime_Value_Tier AS t1
ORDER BY t1.Customer_Lifetime_Value DESC;

I've already indexed the ID column, but the update statement that uses this
query still takes > 1 hour to process! Please help!

 

[Martin J]

Use the Summation button in query design(looks like a sideways M). That will
get you your sum much faster. The Sql looks like

SELECT Sum(

.[value]) AS [totValue]
FROM

;

Use this as your sub query. This will be quite a bit faster that dsum.
Selections will have to be on a form. Dsum is a function whenever you have a
choice between query or function use a query its usually much faster.

HTH
Martin J

 

[John Spencer (MVP)]

This may be a case where you would be better off populating a temporary table
with the values of the Summary query and then using that table (which you can
index) to do the update.

If you choose not to do that, I would make sure that the ID field was indexed.
I'm not sure that the DSum function will use the index, but it may.

Use the Summation button in query design(looks like a sideways M). That will
get you your sum much faster. The Sql looks like

SELECT Sum(

.[value]) AS [totValue]
FROM

;

Use this as your sub query. This will be quite a bit faster that dsum.
Selections will have to be on a form. Dsum is a function whenever you have a
choice between query or function use a query its usually much faster.

HTH
Martin J

Hi all, I've written a query that calculates the cumulative sum of a column.
I've used the DSum function as I need to use this query for updates. It
works, but it's way too slow as I have >60,000 rows to process (Background:
This was originally done in Excel, but I need to do it in Access now because
of Excel's 65K row limit). Does anyone know how I might be able to optimize
this? I've heard that using in-line functions is the fastest, but I'm not
sure how to even go about doing this. Will someone be able to help?

The query I've written is as follows:
SELECT t1.ID, t1.Customer, t1.Customer_Lifetime_Value,
DSum("Customer_Lifetime_Value","06_Lifetime_Value_Tier","[ID] <= " & [ID]) AS
Cumulative_Lifetime_Value
FROM 06_Lifetime_Value_Tier AS t1
ORDER BY t1.Customer_Lifetime_Value DESC;

I've already indexed the ID column, but the update statement that uses this
query still takes > 1 hour to process! Please help!