Alternative to Domain aggregate functions - are there that run faster when generating running sums a

[ryanjames17]

I’m building some big production databases that import oil and gas production data and condition it for analysis. These queries are running on a table that has 50,000 rows to well over 1,000,000 rows. It’s painfully slow when it’s a couple hundred thousand records but takes all day when it’s closer to a million.
I’m running an 8 core machine with 16bg of Ram and a 32 bit version of access. It would be a pain but I could potentially run this on a 64bit machine but I don’t know if that would make much difference.


Are there alternative to generating this data using another function or query? Thanks for your input.

The first query takes a series of sequential dates and generates a running count of the number of months something has been producing.

First Query:

UPDATE [Monthly Production] SET [Monthly Production].[Producing Month] = DCount("[P_Date]","[Monthly Production]","[P_Date]<=#" & [P_Date] & "# And [Entity]='" & [Entity] & "'");

The second query is a Make table Query with a select query nested inside of it. The select query generates several cumulative running totals using the DSum function and then the Make table query writes those results to a new table to make it easier to access the data later.

Second Query:

SELECT INT_RunningTotals.Entity1, INT_RunningTotals.Prod_Mo, INT_RunningTotals.RunDays, INT_RunningTotals.RunOil, INT_RunningTotals.RunGas, INT_RunningTotals.RunBoe, INT_RunningTotals.RunWater INTO y_INT_RunningTotals
FROM INT_RunningTotals;

INT_RunningTotals Query:

SELECT [Monthly Production].Entity AS Entity1, [Monthly Production].[Producing Month] AS Prod_Mo, Sum([Monthly Production].Liquid) AS Oil, Sum([Monthly Production].Gas) AS Gas, Sum([Monthly Production].BOE) AS Boe, Sum([Monthly Production].Water) AS Water, Sum([Monthly Production].[Days On]) AS DaysOn, DSum("[DaysOn]","[INT_RunningTotals]","[Entity1]='" & [Entity] & "'And [Prod_Mo]<=" & [Producing Month]) AS RunDays, DSum("[Oil]","[INT_RunningTotals]","[Entity1]='" & [Entity] & "'And [Prod_Mo]<=" & [Producing Month]) AS RunOil, DSum("[Gas]","[INT_RunningTotals]","[Entity1]='" & [Entity] & "'And [Prod_Mo]<=" & [Producing Month]) AS RunGas, DSum("[Boe]","[INT_RunningTotals]","[Entity1]='" & [Entity] & "'And [Prod_Mo]<=" & [Producing Month]) AS RunBoe, DSum("[Water]","[INT_RunningTotals]","[Entity1]='" & [Entity] & "'And [Prod_Mo]<=" & [Producing Month]) AS RunWater
FROM [Monthly Production]
GROUP BY [Monthly Production].Entity, [Monthly Production].[Producing Month]
ORDER BY [Monthly Production].Entity, [Monthly Production].[Producing Month];

Enlink.zip

Here is an example database with the queries as well.

[June7]

Textbox on report has a RunningSum property. This is the easiest and fastest performing method. Running sums in query and/or form is not easy. If this YTD example does not suit your needs then that leaves domain aggregates http://allenbrowne.com/subquery-01.html#YTD

Also might be of interest
http://allenbrowne.com/AppInventory.html
http://allenbrowne.com/ranking.html

[orange]

Access is single threaded, so I don't think the 8 cores are going to be a big benefit unless they're doing other processes. Do you have any proof/stats that show more than 1 core is being tasked/used by Access?

Domain Aggregate functions are notorious for being slow, especially on larger volumes of data.