What is the best way to monitor a value in a database?

Asked

Viewed 112 times

4

I am working on an item management system, in which I need to constantly monitor the amount of items available. Upon reaching a predefined minimum threshold, the system shall issue the item in an alert list so that it is reset.

What would be the best way to carry out this monitoring? A cron task running every n seconds or a Rigger that will be fired each time the amount is changed?

2 answers

6


A Tigger is the only valid option if you want accurate notification. Something that runs from time to time may work in some very specific scenarios, but "always" will have a lower performance. Unless what you want is not to check from time to time and the update cycle is too high, which does not seem to be the case.

The trigger ensures that every change in the bank makes the notification. You may want this. You may wish from time to time to have notifications of everything. They are different semantics.

The trigger has a cost in each operation, if you don’t need these notifications on time, it can be a cost for nothing.

Making checking batches has an extra single cost and it will do unnecessary operations, but relieves each update and can choose to do so at non-critical time. It won’t change much, but in large volumes it can make a difference. Can you leave the notification for later without problems? Will you do it often? Then I doubt it’s a good option.

Take a test to see which one works best for you. It’s not easy to choose, it depends on the need and how to implement. The danger is in detail.

This site here chooses to update certain things in batch, but some are done on time. It depends on the desired and how much it affects the established performance metric.

In some databases there are better options than using cron.

Of course it depends on the architecture of your solution. I am considering what you want to do in the database. In general I prefer to do in another layer.

3

During the Transaction

If you have a system, it is likely that that system has a service layer or a layer of data access or business rules that handle the transactions that the system is running that prevent direct access to the database.

If that is your case then it would be best for your items transaction to check this Threshold when updating the quantity and triggering a message using the system’s messaging mechanism to generate this alert.

If the system has no layers and allows direct access to the bank by any application then the option of Trigger will more quickly guarantee you recognition of this boundary condition, but triggers can affect database performance.

The "cron" option you are evaluating is the passive form in which you run a query every "X" time. It has the disadvantage that you have to check all the items and this will generate a more comprehensive query in the bank. If the search interval is small it can also bring performance problems. This option would be interesting if you had a separate system table to make these recurring queries.

I prefer the option to do in the transaction of the item itself because then you would have some advantages of the Trigger, which is to act when the change occurs in each item, and to be able to use the application’s messaging mechanism to act asynchronously with that rule and thus not affect the performance of the transactions. You would also get the database independent function.

The downside of choosing the transaction is if you have the rules spread across the system. In this case the best option will be to Trigger even.

In that OS response (in English) has some alternatives for using triggers in database when you want to implement something like a Observer.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.