Given three tables:
(b) Transaction Head
(c) Transaction Detail
We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction date.
We also have a web page where the transaction can be created, edited and deleted.
The problem lies in the query performance. The data in the transaction head and detail tables may increase dramatically fast. Hence, executing a query with a subquery which finds the most recent transaction can be heavy.
We came up with a solution to create a summary table where it may contain the following columns:
(a) branch id
(b) product id
(c) latest quantity ordered
(d) latest ordered date
which will be updated in the transaction module. However, when a transaction entry is deleted, we had to run the whole query, order by date descending, and update the summary table since the previous state is not stored.
Is it advisable to asynchronously update the summary table using the heavy query every time a transaction is deleted? Would not it be too heavy for the server? We are also considering using cron – a job scheduler to synchronously re-compute the summary table for efficiency, but we had to display the information at real-time.
1M/year is 2 per minute. You can do a lot of updating in 30 seconds. 1M/day (12/second) would be more exciting.
Do you need to rerun the summary queries? Can’t you just do a subtract via an
UPDATE? Possibly fast enough to be synchronous.
How often do you need to post a correction? If it is rare, consider adding a row to the summary table with a negative number. Then, when you do your reports, the
SUM() comes out right. (You do sums, correct?)
It seems just wrong to have summary tables but to continually rebuild the table.