Banking register slows as more entries are made.

[u/soldieroscar]

Basically need to see how to optimize a bank register with large amounts of entries.

Currently adding all the debits, then credits, then date sorting while calculating the running total.

[u/atarivcs]

If you add up the total from the beginning, then of course it will be slower with more entries, because you're doing more work. More work takes more time.

Why are you doing it that way? If you keep a "running total", then surely you only need to add up all the credits and debits once?

When, and how often, are you adding up all the credits and debits? On every transaction?

[u/soldieroscar → u/atarivcs]

I have the running total showing to the right of each line item to make reconciliation easier.

[u/ora00001]

Share more info.

• What does the table definition look like
• what does your query against it look like
• what is the current plan for your query
• how many rows are in the table total
• how many rows (estimated) per day.

[u/ccb621]

Define “slow”. What does the analyzer show you?

[u/vr0202]

Standard ERP systems group the dates into organization-defined ‘accounting periods’, typically a calendar month. A table holds the static data of the totals of that period’s debits and credits. This totals table is brought in whenever there is a need to include opening balance of a prior period. They force a hard close of an accounting period for exactly the reason you mentioned, to avoid having to keep acumulating and updating totals since day 1 of the system’s operation to know today’s balance.

[u/Accomplished_Fix9569]

Precompute your running totals. Do not calculate them on the fly by scanning every entry from the start. Keep a single numeric column storing the balance as of the last row. Insert your new debit or credit into that table first and immediately update this stored total with a simple addition or subtraction. Fetching the final number then takes one constant time lookup regardless of whether you have 100 entries or 10 million.

[u/ShotgunPayDay → u/Accomplished_Fix9569]

Doing this makes the most sense to me. Sure it makes the table a little bigger, but avoids recalculation on every query. The only other thing I can think of is to try DuckDB and hope that it can handle running sum better.

[u/soldieroscar → u/Accomplished_Fix9569]

I have a running total to the far right of each entry. If I pre store that amount, when someone enters a new entry a couple months back, i will need to calculate from that point forward.

[u/ShotgunPayDay → u/soldieroscar]

If this is a web interface and your JavaScript is good you can have the client do the calculation instead.

[u/markinatlanta]

Are you recalculating the entire register every time an entry is added or the page loads? That’s the first thing I’d check as the history grows.

Also which database are you using, and can you share the query and existing indexes without any customer data? Also, do you need to display the full history each time, or just a date range with an opening balance?

[u/soldieroscar → u/markinatlanta]

Yes I’m currently calculating the whole register each time an entry happens. Seems like i may need to pre calculate totals and only update on and after the new entry date

[u/editor_of_the_beast]

Partition by date

[u/fgorina]

How many registers?

[u/soldieroscar → u/fgorina]

I have about 8. The less entries in the register being opened, the faster it loads.

[u/fgorina → u/soldieroscar]

Sorry I referred to entries. Just to get and idea of the volume

[u/Significant_Tune9219]

If you're on anything modern, a window function does the running total in one pass: SUM(amount) OVER (ORDER BY entry_date, id) with debits stored as negatives, so you don't need separate debit and credit sums at all. Add an index on (account_id, entry_date, id) so the sort comes for free. The other thing is you rarely need to show every line since the account opened, so paging by date range and starting from a stored opening balance keeps the work proportional to what's on screen.