Let’s assume an application that deals with meetings where some users will participate.
What is an efficient way to achieve this use case:
3 hours before the meeting should start, one reminder mail should be sent to each participant.
It’s pretty easy to trigger a notification based on an Event, let’s say a creation, or a cancellation of any meeting.
However, waiting for the “3 hours before” isn’t an event..because each meeting has its own start date.
An easy way (and surely not performant) would be to have a background-process, that queries the storage (database for instance) in order to find events that matched the rule “within 3 hours before”, and not flagged with some kind of processed.
What about a very high frequency of created meetings… this kind of process would be likely to be inefficient (without evoking the huge impact on the database) to ensure the exact precision of 3 hours (even with a marge of 5 minutes).
Any idea?
I’m not sure that I see what “huge impact on the database” a process would have that polled the database every 5 minutes.
Presumably, you have some sort of table that stores your meetings and their start times. It should be relatively easy to index the column that stores the start time and to write a query that looks for all the meetings set to start between 3 hours and 3 hours 5 minutes from now that would return a relatively small number of records relatively quickly. Adding a column to this table to indicate whether the reminder had been sent or, even better, creating a table that mapped meetings to individuals and tracked whether individuals had been notified (people may be added to a meeting after the 3 hour window) would complicate the logic a bit but the same principles would apply– you should be able to index the data in a way that would allow you to get the subset of data you need relatively quickly.
If you are really exceptionally performance-conscious, your DBA (or back-end developer) likely has a ton of options for tuning these sorts of queries. Those will likely depend on the specific technology chosen (indexed views in SQL Server, materialized views in Oracle, etc.).
0
The most efficient way would be for the client application that is responsible for sending the email to query the DB at startup and remember the next meeting time. It would sleep until 3 hours before this time, then wake up and send the email.
However, the application would also have to be informed of changes to the meeting schedule – either meetings that are added or deleted, and update its next-wake time accordingly. Typically, reading the next-meeting time at each update. How you notify the application of changes is up to you – I would reuse the application as the entry point to the meetings data, so all meeting changes, additions or deletions are sent to the application for processing which then stores the updates in the DB (after all, something has to do this job, might as well be this app).