Email reminders from Events database table

OK so we have a calendar where on the front end a user can create an event with an optional email reminder.
The front end time selector increments by 5 minutes so all times are 12:00 - 12:05 - 12:10 etc and cannot be 12:03

My question is what is the best way to implement the reminders?
Query every 5 minutes?
LVL 25
dgrafxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
How much load you are expecting? Is small number of people, it is okay to go with 5 min query interval, if large having a job to process all logic, get results and put it in a table from where you just select will be good, if even more user, try utilizing in memory db (over kill probably)
dgrafxAuthor Commented:
well - I do want it to scale so that's why I wanted something better than an every 5 minute query.

when you say a job to process logic and insert to table - how would that work exactly?
I need to prepare for event edits where the time is changed and events deleted as well as new events so at what point do I query?

Thanks
Najam UddinCommented:
Event can be very complex, a timing update might have ripple effect on lot of data, I simply meant any thing that require processing for scheduling ,should go in separate job process. It should present you with simple select where id = user id kind of result. If you can simply select data quickly, this simple mechanism will be pretty scale-able.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dgrafxAuthor Commented:
where you say >> I simply meant anything that require processing for scheduling ,should go in separate job process <<
I have to ask - separate from what?
Najam UddinCommented:
Separate from your application, you may have web application or windows application that takes input and displays alerts or calendar for event. You take raw data from application and submit as job to be processed to a separate job component. This component does all the processing like adjusting event date and time , preparing  event calendar, also if you have calendar for group of people you update that, In short all heavy processing is in separate process/component/service.
dgrafxAuthor Commented:
yes ok - normally I'd use a scheduled task.
And this task would run every 5 minutes and query for events that fit the criteria.
So that is why I asked this question - how could I do this better?
ste5anSenior DeveloperCommented:
It depends on the data load as already said which is utilized to query that reminder.

The normal approach would be a email reminder table (queue).

When an event is created, then a row is inserted which holds the trigger date/time. Then you poll this table every minute. Larger intervals would lead to reminders sent to late.
dgrafxAuthor Commented:
ste5an
I had considered something similar to this - whenever an event is created, edited or deleted then mirror that action in a table.

But what I'm wanting to know is how to go about the next step - the polling or querying
And I want to know about the actual sending of the mail - are you thinking database mail or ?
ste5anSenior DeveloperCommented:
Just poll the table. This can be a SQL Server Agent job and database mail. But imho database mail is not really in sending mail at exact points in time.

But when you need more control over the e-mail sending process then I would consider writing my own Windows Services handing the polling and sending.
dgrafxAuthor Commented:
Let me say this:
I've been away due to a funeral and had the flu also

Now I'm back but here is where I'm at.
The last calendar app I wrote I used a scheduled task that ran every 5 minutes to query and send event emails.
Now - years later - I'm wanting to do something "better", if there is something feasible.
I should say the live site will be on shared hosting so writing windows services will not be possible.

Now that I'm back I am asking if the following meets with your approval
1. When event is created edited deleted then mirror that in the reminders table
2. Every 5 minutes will run the scheduled task that will query reminder table for events that are for the next time slot.
MlandaTCommented:
DGRAFX... did you come right on this one?

Windows Service not being an option... there is Hangfire which might allow you to trigger actions at intervals.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgrafxAuthor Commented:
No not really - I kinda forgot about this question - plus the calendar app is on hold.
So I should wrap this question up ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.