|
|
Hi all,
This is not so much a technical question but rather to get the opinion of others with regards to a solution I require.
For the sake of simplicity, lets say I have a database table will a list of scheduled emails - this table contains a schedule date/time and the content of the email to send. These scheduled emails can be created, modified and delete at any time.
My initial thoughts on achieving this is to create a Windows Service (I think this is better than a IIS hosted WCF Service because of the need for timers - maybe I am wrong?). This service will then query the DB table at regular intervals and send any emails that need sending.
But how best to handle the timing? I want the scheduled emails to be sent as close to the correct scheduled time as possible but at the same time I want to limit the querying of the DB (i.e. not every second)
I also thought about notifying the service when a new task is scheduled and then setting a timer up there and then based on the difference between current time and scheduled time. This however may lose precision. Also, what if a scheduled email is modified/deleted? Also, doesn't seem best for schedules of a long time (e.g. a day, a week or a month even)
Any opinions are welcome
Thanks
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
I wrote an article about scheduling: I have used the code a few times in windows services. As far as reducing queries on the database, you can schedule that too.
Scheduling Future Dates [^]
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Thanks for your reply and an interesting idea. Which I guess in your suggestion would be to sleep a synchronous thread until the task is due to run.
I guess my issue here would be when tasks are changed or cancelled. I suppose I could check the task is still valid at the same time you are comparing the dates. But then if I need to re-query the DB I may as well do so at regular intervals. I am not sure I like the idea of sleeping a thread for a week especially one that will be dynamically created.
I have been thinking that scheduled intervals will be limited to every 15 mins (e.g. 12.00, 12:15, 12:30 etc.) - So I could get away with querying the DB every 15 mins (or just short of and sleeping) - that way I can make sure I don't miss new tasks and updated tasks and I don't even need to notify the service when a tasks is added/changed
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
If the schedule changes, you can destroy the current timer thread, and start a new one, or design your threads in such a way as to be able to compensate for said changes. It's a simple matter of implementation.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
But then I have the extra effort of needing to track all timers and the application creating the the tasks would need to notify the service of changes. I just don't think it needs to get so complicated for what is really quite a simple concept.
I do like your suggestion in terms of maintaining precision, I just don't think its right for my needs. Effectively what I would end up doing is creating an in-memory copy of the database table (or at least a cut down version of) in order to identify timers - thou I guess I would only need the ID column values linked to the timers.
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
if you get the mails-to-be-sent from the database sorted by datetime ascending, your app (Win Service) can send the ones it considers "immediate", then delay itself till the time arrives that corresponds to the first one that hasn't been sent yet. The only caveat is there could be new requests added while your service is asleep.
|
|
|
|
|
Thanks for the reply.
Yes, it is almost certain that new ones will be added while the service sleeps - this is why I was thinking of limiting the scheduled intervals to 15 minutes and querying the DB every 14.9 minutes in anticipation.
On a second thought, what if on first run of the service there are no tasks to be processed? I would definitely need to query regularly in that case.
Wouldn't it be nice if there was a system event you could hook up by specifying the date/time you wanted it to fire, like...
System.Schedule.FireAt(ProcessFunction, DateTime.Laters);
...thou I would still need to validate the task, and on seconds thoughts, that's pretty much what a timer does 
So, query every 15 mins? See any flaws?
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
If the data changes, you basically have three options:
1.
only allow changes through your web service itself, so it knows at all times what is going on.
2.
organize your database so it provides events to your web service; the possibilities will depend on your DB. And I can't help you out here.
3.
implement a polling scheme, i.e. have your web service query the DB regularly.
Depending on the exact needs you may prefer to have a mix of the above.
|
|
|
|
|
Thanks Luc,
I am starting to implement 3 but 1 is something I hadn't thought of - thou I would have to rely on the database not being tampered with from other sources which I don't like the thought of
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
You're welcome.
|
|
|
|
|
A step to remedying the issue of someone updating the table from outside your application is to add INSERT or UPDATE triggers on that table. The stored procedure can send a pipe message or email to wake up your monitoring thread to amend it's plan. I've used similar techniques for situations in which multiple events need to be accommodated; in your case it's:
1) New email added to the queue with a send time earlier than the earliest current one
2) Message from SQL Server to alert of a change in the queue table (via SMTP or named pipe)
3) Trigger time to send the first email(s) out
It sounds complicated, but the implementation isn't actually all that difficult. And it's a handy tool to have in your arsenal for future situations.
|
|
|
|
|
Well, do you really want your service to work with data in the database that has been tampered with?
Depending on the number of scheduled emails (due to memory constraints), you could have the service be the sole keeper of the email information - and only write to the database as a backup store. Then you have much more control over the process - the service gets and tracks the requests - if it is caching to the database (or other store), then it can control how long in the future to cache, and so will know when to pull from the db again. (Imminent tasks can also be stored in the db for failover purposes, but won't have to be looked up unless the service is restarted for some reason).
|
|
|
|
|
I implemented a scheduling feature in our application server, it works with a thread wich calculates the amount of time to sleep until the next action. If a new action is added, the thread is interrupted and it computes a new wake up delay.
|
|
|
|
|
What about getting SQL Server agent to do the scheduling? It's rather good at this. That way, you could create a job to run every minute say which just runs the query and appends the new emails to a queue table. Because all that's in the database domain you can make it transactionally safe etc, and should your windows service go down, you'll have everything queued up when it comes back up.
Then, you can poll your queue table quite frequently (say every second) because most of the time its going to be empty.
Just a thought.
Regards,
Rob Philpott.
|
|
|
|
|
Thanks. I see your logic but perhaps this is a flaw in my simplified requirements. The actual tasks that are going to run are not just emails being sent but the service will process other DB data and provide that information like an export function.
But I like your idea in terms creating a queue table, I will try to keep that in mind if the need arises
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
musefan wrote: other DB data and provide that information like an export function.
Actually you can do the entire operation within the (SQL Server/Oracle) database, including send the emails. This is how it used to be done before these new fangled service thingys turned up. SQL jobs have fallen out of favour in the last few years but the DB has a long history of doing this precise operation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have never done anything like that in SQL Server, but I imagine it would not cover all my needs. Thanks anyway thou
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
Trying to cycle every second is likely needless. I also wouldn't create a thread for each event.
I use a System.Timers.Timer for each of my Windows Services. For my Scheduler, I set it to poll every fifteen seconds (but it's configurable). When it cycles, it queries the database for any due tasks and executes them, be they reports or emails or Westminster Chimes or whatever. Sub-fifteen seconds ought to be timely enough for anyone, especially where email servers are concerned.
Also, each time the Service cycles it updates a timestamp in the database, so that I can tell that it's still cycling and not just hung up or dead.
One thought about sleeping for a future event is to sleep for only part of the time (90% for instance) and then sleep again until the duration is under some threshold.
|
|
|
|
|
I have done something similar to this using Quartz[^]. Quartz allows you to write your own 'job' class and to add it to its scheduling service with a specific firing time. I poll my DB table for new/changed jobs every few minutes, and submit the jobs to Quartz, which then takes on the precise timing without me having to worry about it. (Quartz is very good for scheduled - i.e. repeating - jobs and it doesn't sound like you want that, but worth a look nonetheless.)
Adam
|
|
|
|
|
I am using Quartz.NET for quite a while and I am very happy.
Especially creating own jobs based on existing interfaces made my life a little bit easier 
...I totally agree that it might be worth a look.
best regards
Andy
|
|
|
|
|
Databases and Regular Files (stream of bytes stored on disk) are both powerful mechanisms for data storage and retrieval. Remember that each has it's strengths: point is to take a moment to think if perhaps the database being polled for work versus a special file you devise (for speed's sake) that allows quick answers to the "Polling: Do what Now? question"
Second - There is a tool called SEC: Simple Event Correlator
I think you'll find this isn't as far removed from what you're trying to do as it might first look... this is the kind of system you are talking about - for general scheduling.. then you can toss in business logic to do all kinds of stuff.
The premise is this thing works by reading log files... and applying "rules" to them./.. basically.. if Log file A says "Server Down" ..say... you toss in rule.. 4 times in an hour - then this SEC thing would see the condition was met, then fire off some other task you said to do in this condition... say email IT server support, call the troops.. Perhaps if the same program see that 2 hours later server is still down - it sends out a email to let folks know "system X" is offline and is undergoing maintenance...
I'm all about writing fresh new cool useful systems when I can manage it.. but in this case.. I think you might have a few systems to pull from that you can "configure-code-customize" to do exactly what you need without to much heads-down new-development other than implementation work to automate what you need done... like sending those emails you were asking about
[edit]Conclusion: By using SEC or Splunk or other log file analyzing rule based engine - and perhaps simply writing a line of text to a log file to cause something to fire... or by just monitoring log files for "triggers" to respond to - action items... might work as a direct alternative to simple scheduled "cron" or "system tasks" being queued up.
[/edit]
Know way too many languages... master of none!
|
|
|
|
|
Thanks for the very detailed response - But I am sticking with a database table for many reasons.
I have already implemented a lot in this service I have created - but very little of it is actually used for the scheduling. Basically all it is is...
- Using one timer
- On start service, Reset timer based on difference between NOW and next scheduled intervral (every 15 minutes of the hour)
- On timer Tick, kick off a sync process to do what needs doing. Then reset the timer
- and on, and on, and.. hopefully on a bit more 
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
musefan wrote: next scheduled intervral (every 15 minutes
For that long a duration, I'd consider a Windows Scheduled Task.
|
|
|
|
|
It is configurable so it may change depending on how things go but after getting into the implementation (and how little is actually timer related) I can see your point for just having an application that just runs when scheduler tells it to.
I guess I shall just wait and see how things go, maybe there will be more to this service to come
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
