We have developed an application using ASP Net Core where uses have approval to be approved. The user will receive emails for pending approval at the time specified by the users.
Example
user1 sets the time to get email at 12.55 pm
user2 sets the time to get email at 4.00 pm
We have saved the user set time in the database and currently we are running an Azure Function app on a timer trigger. The trigger will run every 15 minutes and checks the SQL db for user notification time. So if a user sets the time to 12.31 pm, he will receive the email after 12.45 pm once the trigger run every 15 minutes. The problem is the user has to wait 15 minutes and the email is not on time.
We have checked on HangFire Recurring jobs, but the problem is it calls our SQL db every minute which is causing a performance impact.
Is there any better solution to send the email notifications at the user specified time and without calling the SQL database every minute to check if a specified user time is available to send notification?
You can use a queue with a delayed delivery.
Essentially when an entry is created you would push a message into a queue and delay its delivery by calculating DesiredTime - Now. Then you simply implement a queue listener. At the desired time a message will pop into the queue and get handled by your worker. You then push a new message into the queue delaying until the next desired time.
With this solution you can pend a very large number of emails with nearly 0 pressure on your database. It will scale the handling of the emails horizontally and will have pretty good reliability right away. The cost is also pretty low depending on the queue you use.
You can use Azure Storage Queues for this.
CloudQueueMessage message = new CloudQueueMessage(requestBody);
// The message will be visible in the queue after 3 days
queue.AddMessage(message, initialVisibilityDelay: TimeSpan.FromDays(3));
You may need to do some extra things to make sure this is reliable, such as enabling dead letter queues and have a re-queueing mechanism in case there are errors processing the messages.
initialVisibilityDelay
Nullable<TimeSpan>A TimeSpan specifying the interval of time from now during which the message will be invisible. If null then the message will be visible immediately.