Query taking awfully long to process :( (1 Viewer)

Hi,

I am trying to create a running total using DSUM in an example list below:
But it is causing my machine to lag so Im wondering if my code is wrong

Customer Date DC Balance
Client 1 14/01/19 10 10
Client 1 14/01/19 15 25
Client 1 25/01/19 30 55
Client 2 14/01/19 20 20
Client 2 25/01/19 70 90


This is my sql query below:
SELECT
DSum("[DC]","[Cleansed Consolidated]","[Customer]='" & [Customer] & "' And [ID] <=" & [ID]) AS Balance,
*
FROM [Cleansed Consolidated];

Im not very pro-efficient in Access unfortunately

Many thanks for your help

That DSum is resource intensive. Another way to achieve what you want is with what is called a correllated subquery (https://en.wikipedia.org/wiki/Corre...a SQL database query,query, it can be slow.):


Instead of a doman function (DSum) it runs a query for the balance.

Also a few notes--'Date' is a poor choice for a field name. It is a reserved word (https://support.office.com/en-us/ar...-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2) which means using it as a name makes life more difficult when coding and querying. Same issue with non-alphanumeric characters in table/field names. That space in your table name should be removed--it just makes things more difficult.

Lastly, are you sure ID field is the correct field to order your data by? I mean you have a date field--shouldn't that be the field that determines the order of your data? Theoretically a record could be put in for 5/5/2019 and then a record for 1/1/2019 could be put in. That means the 5/5/2019 record would have a lower ID than the 1/1/2019 record and would be added to the balance before the 1/1/2019 record.

how about if you use subquery:

I'm presuming that your real problem is in the larger table that your sample represents. The use of a Domain Aggregate (e.g. DSUM) is part of the issue. Each iteration through the loop that is internal to the SELECT query has to run a DSUM. But a DSUM is actually an implied query of its own. So if you have, say, 1000 records in the main table, you will run 1001 queries - the outer query and 1000 inner queries. DSUM is great in VBA code but will be a drag (literally, making things drag along...) when used in SQL.

By the way, even if only in a query, never use reserved words as field names. Not that you can't, but Access HAS been known to get confused... and DATE is a keyword.


This technique is called "sub-query" and gives you advantage in this way: SQL doesn't have to stop and wait for the domain aggregate query to be constructed and run for each row, because DSUM is an external function. Here, SQL can see everything and build what is called a "query plan" to isolate everything for you. That sub-query is now VISIBLE to SQL whereas the DSUM, being a function, is a black box that requires a context switch that pauses SQL processing for each implied row.

EDIT: I see my colleagues chimed in before I did, but they apparently didn't notice that you had ordered results in the desired query results, so you will need to use the ORDER BY clauses to keep the input in the right order to get that result in the output.

Note also that having a space in an object name is legal but means you will forever need the square brackets around it. IF you shortened the name or just removed the space, you would type two less characters (the []) every time you referred to that object.

Finally, I appeared to have broken my own rule about using a reserved word, but I'll explain: The "As xxxx" clause is a way to force a name on the field in the query. As long as this is a display-only query, that usage of a reserved word isn't likely to blow up on you. HOWEVER, if this query becomes input to ANYTHING else - a form, report, or another query - then definitely don't use DATE as the name of the column.

Many thanks for your help
I will try the query above.

Indeed the total records in my consolidated table is 255K and growing.

I usually use excel for everything but this time I need a dynamic sheet/table per customer that updates every time my consolidated table grows.

I have ordered the table by customer and date, then added an ID field using the design view autonumber.

My idea was to have a select query selecting all and a DSUM to create the running total needed per customer.

I tried searching for methods to do that and that query is the result, but is there a more efficient way to do it?

Also good point for the reserve words I will change it.

another method is to join the table to itself with a non standard join (in red below) - i.e. can only be done in the sql window


Ensure Customer is indexed (I presume tDate already is)

You don't have to write this in the sql window, use the query builder as normal (i.e. the join in red is =). then go into the sql window and change that particular join.

If you have lots of data then this will be faster than other methods but still relatively slow because you want the whole dataset (typical excel thinking)

In reality you perhaps only want to see one customer at any one time, so include a WHERE clause in the above query

Whichever method you decide to go with, the other issue you may have is if you have two transactions for the same customer on the same day. With the above example it won't matter because you are not getting a total by transaction, only for the day. If you need to show each transaction, the usual solution to this is to include a time element in your date field. Imagine the scenario where an entry is back dated

id...customer..date.........DC
1....X............1/1/2019..10
2....X............2/1/2019..11
3....X............1/1/2019..12

both records 1 and 3 have the same date so <= will treat them the same.

customer..date.........DC
X............1/1/2019..22
X............2/1/2019..33

But if you include the ID you'll get

id...customer..date.........DC
1....X............1/1/2019..22
2....X............2/1/2019..33
3....X............1/1/2019..22

So the alternative is you'll need to include another join, T.ID<=M.ID so you should then get this

id...customer..date.........DC
1....X............1/1/2019..10
3....X............1/1/2019..22
2....X............2/1/2019..33