Running Totals

[jlgray0127]

I have a query I'm needing some help with.


I'm calculating running totals on approx 100K lines.

Now, for a couple reasons I am inserting into a table.
One, I only update the data once a day, so I do not need to requery the same data over and over again.

Two, I need to be able to analyze this data and value from the calculation to a related table.

I can go into more detail if needed, for space sake, lets just assume this theory, while not ideal, works.

I'm wondering if anyone has thoughts on how to improve this. My SQL is below. This query, if I only calculate against 20-25K lines and runs in about 3 minutes.
If I exceed 25K lines (up to even 30k, adding just 5K lines) the query will run for almost 2 hours before I just kill it. If anyone has any thoughts on improving, it would be greatly appreciate!!!Thank you in advance!!!!

INSERT INTO BoM_ItemAnalysis_RunningTotal_TL ( ID, Balance, OH, TotalQty, P_SOL )
SELECT Sys_ItemAnalysis_T_Sub.ID, [OH]-DSum("[TotalQty]","[BoM_ITemAnalysis_T]","[ID]<= " & [ID] & " And [Component] = '" & [Component] & "'") AS Balance, Sys_ItemAnalysis_T_Sub.OH, Sys_ItemAnalysis_T_Sub.TotalQty, Sys_ItemAnalysis_T_Sub.P_SOL
FROM Sys_ItemAnalysis_T_Sub
WHERE (((Sys_ItemAnalysis_T_Sub.P_SOL)<=(Date()-(DatePart("w",Date(),2,1)-1))+42));

[Micron]

As I see it, one of the worst aspects of that query is that for each record of 25K records you are summing the entire set of records. DSum is an aggregate function that operates on all records in a field, subject to criteria, so there can only be one value returned by the function (unless I'm missing something wrt what your query does). To do that 25k times is, well - crazy. Add to that, you are performing the DatePart calculation 25k times. That one may be different for many/all records, so

- get the Dsum value and pass it to the query
- if the datepart calc will be the same for all, then do the same. If not, perhaps build a subset of records according to that calculation and link to it.
The second point may not be necessary if you eliminate 25K DSum calculations.

[June7]

Domain aggregate functions are known to slow query performance. However, so can nested correlated subquery, but maybe not as much.

Try this test:

SELECT ID, [OH], (SELECT Sum(TotalQty) AS ST FROM Sys_ItemAnalysis_T_Sub AS Dupe WHERE Dupe.ID <= Sys_ItemAnalysis_T_Sub.ID) AS RunSum
FROM Sys_ItemAnalysis_T_Sub;

If your data was in a SQLServer db, there is a nice built-in function to do this, presumably faster.