I have an app tracking inventory being shipped and received along with some other data. We use it for a daily report for tracking product amounts. Would there be any any benefit to keeping a running balance with each transaction? Or would just calculating balances when needed be sufficient? The downfall I see to keeping a running balance would be if I would go back and adjust a transaction then all subsequent balances would be off. Adjustments would have to be entered as a separate transaction for the balance to keep tracking. I was just wondering what the difference would be in getting the balance from the last record when keeping a running balance as opposed to getting the balance from the net sum of 100 or 1000 records. I’m still struggle to setup SQL tables properly so queries run efficiently.
There’s no suited answer to give to this.
The only way is to measure things when you notice some slowdowns (measuring queries that are fast anyway is a waste of time, because both read and write timings change in different ways when data grows).
However, some hints anyway:
- Calculating the sum of 1000 values should be pretty fast in SQL (certainly if you don’t need to filter those out of a bigger table)
- Watch your indexes: make sure that every query where you filter or sort on a lot of records uses an index. But don’t define indexes that will not be used. Indexes need to be updated whenever there’s a write, so they slow down writes.
- Create a good clustered index: a clustered index is actually the order of the records in that table. So a table can only have one clustered index. You need to choose this one wisely: new records are best added to the back (so the clustered index should always increase), and it should be unique when possible. If there are multiple candidates for the clustered index, try to use the one that’s most often used as filter/sort order.
- You can also consider materialized views, though they do possibly make writing a lot slower (it needs to recalculate the sum on every write, which is more work than maintaining an index), so they’re very suited for queries that need to do a lot of calculations on quite static tables. https://en.wikipedia.org/wiki/Materialized_view
- Tweaking the locking settings of the DB is also possible. Removing a lock from the read operation makes sure that the writes aren’t locked while reading the data. However, it may cause dirty reads. So it’s dangerous if you want to further process the data. But should be fine for display purposes.
1 Like
It is common to use an “after” trigger on your transaction table to perform the corresponding update to your inventory status table. You have to take care that the update takes an exclusive row lock within the transaction to serialize updates to specific running totals.
1 Like