what is the best way to create warehouse stock report make SQL query or table updated by triggers

Question

Now I want to make an online stock report balance sheet calculate the result from hundreds of thousands of records from so many tables
I have two ways to do that
first way:
Make SQL View query calculate the result from all transaction and get the result JIT
second way:
make stock balance table and update this table by triggers run in each transaction.
what is the best way to get balance sheet reports?

Answer 1

If I understand correctly, you want input on how to generate your stock balance report. This type of analysis usually boils down to a couple factors: concurrency, performance and maintenance. My comments:

1. Dynamic calculation (many tables, hundreds of thousands of records)

Writes: stock table
Reads: stock table

• PROS: better concurrency / throughput (only one write)
• CONS: potential for slower reports (if tables grow too big and indexes are not maintained)

This is the better option if you have lots of transactions and want to avoid locking issues. You avoid having to update separate tables / structures when you update your stock table.

2. Maintain summary table via triggers run after each transaction

Writes: stock table, summary table
Reads: summary table

• PROS: Fast reports (if you allow for "dirty" reads)
• CONS: slower writes, potential for more locking issues

If you don't have a lot of transactions and you want fast performance, then this is worth looking at. Just keep in mind you have to do two UPDATEs, so your write operations will take longer. If you can do a "dirty" read (i.e. access the summary table without a READ lock), then this should give you very fast reports.

Another option to look at is indexed (materialized) views, which is like a hybrid version of the two options above: Indexed views