How to Calculate Stock Quantity with Millions of Records with mORMot2?

Lam · 2024-09-12 16:05:45

Hi everyone,

I'm currently facing a performance issue in my application related to calculating stock quantities. The calculation involves summing up stock movements (in and out), and with millions of records and a lot of users accessing the data, the process is slow, causing the app to freeze and sometimes even result in deadlocks.

To address this, I'm considering building a separate app using mORMot 2 to handle these calculations more efficiently and avoid deadlocks. However, I'm not entirely sure if mORMot 2 is the right tool for this or how to best approach the implementation.

Has anyone dealt with a similar issue or used mORMot 2 for high-performance calculations? I would appreciate any advice or insights on how to set this up properly to improve performance and reliability.

Thanks in advance!

ab · 2024-09-12 16:31:04

Which DB are you using?
I guess you make a simple SELECT over the whole data tables, and it takes a lot of time, is it the case?

If the bottleneck is the database, switching to mORMot 2 won't make it faster.
Couldn't you use a map/reduce or an aggregation pattern instead, even on your SQL DB?
For instance, creating per-day or per-months aggregated results, then using those for the computation?

PierceNg · 2024-09-13 00:57:26

Lam wrote:

Hi everyone,
I'm currently facing a performance issue in my application related to calculating stock quantities. The calculation involves summing up stock movements (in and out), and with millions of records and a lot of users accessing the data, the process is slow, causing the app to freeze and sometimes even result in deadlocks.

The usual architecture is to implement a job queue: The main app puts a job on the queue and immediately returns a "processing..." response to the client. Other worker apps take jobs from the queue, do the work, and communicate the outcomes by notifying the main app thru the queue or app database, notifying the client directly thru email/WhatsApp/Telegram/Discord etc.

But as ab wrote, if your database is the bottleneck, then another approach is needed, like running your DB on bigger iron or rethinking the database setup.

Lam · 2024-09-13 08:15:54

Thank you for your responses!

I'm using Firebird 2.5, and you're right—I'm currently using a query like this:

SELECT SUM(tqtt) FROM (
SELECT SUM(qtt) AS tqtt FROM det_achat d
INNER JOIN achat a ON a.achat_id = d.achat_id
Where a.validated='1'
UNION
SELECT -SUM(qtt) AS tqtt FROM det_vente d
INNER JOIN vente v ON v.vente_id = d.vente_id
Where v.validated='1'
)

I understand that switching to mORMot 2 alone won’t necessarily make it faster. My plan is to send the product ID to another app that performs these calculations. The results don't need to be in real-time; they can be queued and processed asynchronously.

Regarding your suggestion about creating per-day or per-month aggregated results, I might consider that as a last resort if the other approach doesn't work out. I'm hoping that offloading the calculations to a separate service will help reduce the load on the main app and avoid the freezing and deadlock issues.

Thanks again for your insights!

Last edited by Lam (2024-09-13 08:17:54)

squirrel · 2024-09-13 08:33:51

Its easy to check if this is a db or code issue, just run the query directly on your preferred db tool and see how long it runs without any code or services involved. My guess is that you should be able to reduce the query runtime by correctly indexing the tables.

I've seen how correct indexes can dramatically reduce query times, but also the reverse. Adding and combining indexes that are not used will increase insert times, and increase the chances of the db engine using the wrong indexes - slowing down search times.

ab's suggestion of aggregation is very valuable and makes a big difference. It is something you should seriously consider. Grouping data in separate summary/reporting tables and populating/updating them at fixed intervals provides a lot of stability and speed. It means only a small number of records are queried by reports, and also that running those reports does not impact the real datatables that are used by operations.

Lam · 2024-09-13 09:59:23

I've confirmed that the bottleneck is indeed the database, and the indexes are set up correctly. When the database size was below 1 GB, the query performed smoothly, but as the data grew, clients started experiencing performance issues.

For a quick solution, I’m inclined to follow AB's suggestion about using summary/reporting tables,
doesn’t this approach break the principles of database normalization and design?

Looking ahead, we're planning to use mORMot in the next version of our product. As our application continues to grow and becomes harder to maintain (currently using RAD methods), and we're considering adding more platforms.

ab · 2024-09-13 18:27:27

In the relational/normalization SQL model, you can achieve atomicity using triggers.
Our ORM does not support them, but you can define them with plain SQL.

Even SQLite3 supports triggers.
https://www.sqlite.org/lang_createtrigger.html

rdevine · 2024-09-16 08:56:43

doesn’t this approach break the principles of database normalization and design?

No because you're basically creating a small data warehouse. These are usually built using batch ETL processes and contain aggregates of your normalised data, which fits with your use case. You also have the option of putting the aggregates into a separate columnar database if you need even more speed - maybe something like DuckDB.

mORMot Open Source

#1 2024-09-12 16:05:45

How to Calculate Stock Quantity with Millions of Records with mORMot2?

#2 2024-09-12 16:31:04

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

#3 2024-09-13 00:57:26

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

#4 2024-09-13 08:15:54

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

#5 2024-09-13 08:33:51

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

#6 2024-09-13 09:59:23

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

#7 2024-09-13 18:27:27

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

#8 2024-09-16 08:56:43

Re: How to Calculate Stock Quantity with Millions of Records with mORMot2?

Board footer