Given three tables:
(a) Product (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.