Pages: 1

#1 Re: mORMot 2 » How to Calculate Stock Quantity with Millions of Records with mORMot2? » 2024-09-13 09:59:23

Lam

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.

#2 Re: mORMot 2 » How to Calculate Stock Quantity with Millions of Records with mORMot2? » 2024-09-13 08:15:54

Lam

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!

#3 mORMot 2 » How to Calculate Stock Quantity with Millions of Records with mORMot2? » 2024-09-12 16:05:45

Lam
Replies: 7

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!

Pages: 1

Board footer

Powered by FluxBB