5

If I had a lot of messages in a database that I wanted to send, and each row specified a date and time to send the message, and a flag for if it has been sent.

These won't always be at fixed intervals, and more than 1 message may want to be sent at the same time.

In this case it would just queue them up and send in order of when they were created.

Is the easiest thing to do just to have a function that runs over and over again, once it completes it just runs again

So it would:

  • Start Running and check the current date/time
  • Check for any unsent messages
  • Send all the messages due to go out before and up to the time it started running
  • Start all over again and take the current date/time

My problem with this is, would it just be horribly inefficient to continuously have a method running, possibly for hours or days without actually sending a message.

The main strain in this case I think would be put on the database, it would constantly be getting hit with a query.

Is there a better way to schedule something like this to happen.

Or just do the above but every time it runs make it wait for 5 minutes before running again.

Does Workflow 4 offer anything suitable for scheduling perhaps?

Dan Harris
  • 1,336
  • 1
  • 21
  • 44
  • 2
    `Thread.Sleep`? Or [Task Scheduler](http://en.wikipedia.org/wiki/Windows_Task_Scheduler)? – Merlyn Morgan-Graham Nov 18 '11 at 14:05
  • 2
    or [Timer](http://msdn.microsoft.com/en-us/library/system.threading.timer.aspx) – H-Man2 Nov 18 '11 at 14:10
  • Thread.Sleep would sleep for a fixed time, but I need a function to be called at various different times. Task Scheduler would mean setting a task up for each message to be sent. In theory this could work but if there were 20 messages going out, i'd have to schedule 20 instances of a console app or something to start up afaik. – Dan Harris Nov 18 '11 at 14:10
  • If you knew when the next messasge was due to be sent, you could sleep until that time. Ie, poll now; send all messasges; get date time of first message not due yet. Then calc time diff until then and sleep - however, will new messages be added to the queue with a time that's earlier ? If so, you would need to react to an event when new items added to queue to check the time, or need to poll at your minimum acceptable rate (ie, add item now, but next poll is X min/sec away. Andrew – andrew Nov 18 '11 at 14:12
  • No, we're actually suggesting to automatically re-run the routine every 5 minutes or so, and if there are no messages to send - do nothing. – Zruty Nov 18 '11 at 14:12
  • Based on your comment above, can you remove previously scheduled times ? Ie, if the next scheduled time is 10:00, and i've got a new item at 10:15, just add to queue. If it's a 9:50, then task schedule this, and remove the 10:00 task. Then at end of each task, figure out what the next run time is and schedule another task. – andrew Nov 18 '11 at 14:14
  • So... how did this manage to get 13 answers? – MPelletier Nov 18 '11 at 14:28

13 Answers13

2

You could always do a pre-emptive read of the next time value in the series and do a single sleep until then, instead of looping through short sleeps over and over.

Not sure if that's as elaborate as you want though

Jean-Bernard Pellerin
  • 12,556
  • 10
  • 57
  • 79
  • Essentially I think the more simple I can keep it the better. Keeping a record of the next date/time could work well. Then it would be possible to re-check that next time when a message is added, in case it is inbetween the current time and the next scheduled interval. – Dan Harris Nov 18 '11 at 14:15
1

Maybe have a compiled view in the database which returns messages that are not sent (I assume there's a flag on each record?) and for which the intended send time is prior to the current time. Then a Windows Service or console application on a scheduled interval can hit that view (which can be performance-tuned in the database pretty well, I'd imagine) and send any messages returned by it.

David
  • 208,112
  • 36
  • 198
  • 279
1

You could use a windows service to accomplish this. Or if you're using MSSQL, you could even use a SQL Server Agent Job.

twaggs
  • 3,591
  • 1
  • 14
  • 8
1

Several answers has suggested sending some messages then calling sleep until the next message is due to be sent.

How you sleep in this instance is all important.

You can - in theory - tell a thread to sleep for hours, however if during that time the app (or service) needs to shut down then you're in trouble. The process will be terminated, no cleanup will be executed. This is a less than ideal.

Don't get confused between the concept of polling for work to do, and sleeping between polls.

If you've to wait 5 minutes (or 5 hours) before next polling the database, that's fine, however you never want to *sleep for more than a second or two at a time.

What I'd do . . .

Write a windows service. The service has one active thread that polls the database, see's are any messages due to send, and sends them. It will then poll on a configurable delay (1 minute, 5 minutes, 1 hour, what ever suits).
However it will never sleep for more than a second while it's waiting to poll the database.

If you can be assured that messages can only be added to send after the last message in the DB? If so you can check the time of the next message and not poll until that time.

However, if I find that the next message doesn't need to be sent for 5 hours, is it possible that while I'm waiting a message was added that should be sent in 30 minutes?
If so then you can never trust the "Next message time" and not poll until then, you have to continuously poll on your fixed interval NB worth saying again, your polling interval and your sleep interval are not the same thing.

Binary Worrier
  • 50,774
  • 20
  • 136
  • 184
  • How much stress would polling every minute say, have in general. It would basically be doing a SELECT on a table every minute. If the table was cleaned up regularly with send messages being archived off, I guess polling regularly wouldn't cause much of a problem. – Dan Harris Nov 24 '11 at 15:51
  • @Dan: A simple SQL query every minute on a well designed table will have a negligible impact on the server. Even if the table contains _millions_ of rows, if it has well designed indexes the query should be very fast. Hitting a database once a minute shouldn't be a problem, in fact - _massive generalisation follows_ - SQL Server running on a reasonably spec'd multi core machine should be able to handle hundreds (if not thousands) of such queries a second without raising a sweat. That said I've seen simple queries on 300,000 rows take 4 seconds to run when SQL was hosted on a base bones . . . – Binary Worrier Nov 24 '11 at 16:00
  • . . . single CPU, 512MB RAM (Virtual machine). **EVEN THEN** SQL worked, running your query once a minute? Not a problem. P.S. The client was wondering why our software was running slowly, our response? "Well obviously you have it running on our minimum recommended hardware?". Client "Ehhh . . . we'll get back to you" – Binary Worrier Nov 24 '11 at 16:03
0

How about writing a windows service which does this for you. This Windows service will run in the background and check the current time with your db records in a purticular interval (ex : every 5 minutes) and send emails to people and update corresponding records in your tables to set the Email Sent Flag to true

You can even have an SQL job which selects records which are not sent and matches wtih the current time and call a stored procedure which calls dot net assembly to send email . The dot net assembly can use SMTPClient to send emails.

Shyju
  • 214,206
  • 104
  • 411
  • 497
  • A windows service is my first thought for how it could be done. Just scheduling the function to run at the right time without "over-running" it constantly. – Dan Harris Nov 18 '11 at 14:17
0

It depends on what you use. Using a scheduled task or a service is perfectly acceptable for the scenario you describe.

You have to be careful though that you do not tie up resources if the process runs too often. It might be more efficient for it to run less often at peak times and more often during off-peak times.

Digbyswift
  • 10,310
  • 4
  • 38
  • 66
0

Whatever method you prefer (make a Windows Service, use Task Scheduler, etc..), please bear in mind that your initial suggestion is exactly what is called busy waiting, which is something you should avoid unless you really know what you're doing.

Zruty
  • 8,377
  • 1
  • 25
  • 31
0

What you describe isn't that bad if you extend it with "when there are no messages due select the next time a message will be due and sleep till then".

Alternatively use a DB with "notification support" making the whole thing event-driven i.e. the DB sends you an event whenever a message is due.

Yahia
  • 69,653
  • 9
  • 115
  • 144
0

I recently implemented a windows service which utilized a class called IntervalHeap in the C5 collection class library. I then added a persistence layer which keeps tracks of the items and their intervals in case the service is stopped/crashed.

Has been in production for a few months and has been working very well.

Paulie Waulie
  • 1,690
  • 13
  • 23
0

you can use this one .NET Scheduled Timer for checking timeinervals and running the function(sending messages) at specific time intervals ....

Glory Raj
  • 17,397
  • 27
  • 100
  • 203
0

I would say create a windows service with the timer. it may sleep for configured amount of seconds and then compare the datetime from the database. if it matched then send an e-mail & set the flag in the database for sent e-mails.

VRK
  • 400
  • 2
  • 5
0

We do this at a financial institution to send out internal e-mails from our intranet applications. Once every 15 minutes, a scheduling software (enterprise scheduler, not a Windows scheduled task) fires off a job. We have a view called PendingEmail on top of a table called EmailQueue that only lists out what needs to be sent this go around (the EmailQueue table has a PopDate, which is an effective date as to when the e-mail should get sent). The application fires off e-mails for whatever it found in the PendingEmails view.

The job sends out a maximum batch size of emails every 15 minutes, marking each record with whether it was successfully sent or whether there was an error (invalid email address, etc.) and what the Exception was, and whether we would like to try re-sending it the next time around. It updates that EmailQueue table all at once, not each record individually. The batch size was put in place to prevent the job from taking more than 15 minutes and stomping on itself.

I don't know that polling every so often is really consuming all that many resources, unless you're going to do it every 5 seconds or something. If you're sending out millions of messages you may need to distribute the work across multiple machines. If you're going to write some custom code, I would use a Timer over Thread.Sleep(), and set the Timer to tick every 5 minutes or whatever interval you'd like to perform work. An event fires on every tick that would subscribe to to start the routine that sends your messages.

See this post on Thread.Sleep() vs. the Timer class:

Community
  • 1
  • 1
Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
0

Many databases allow events to be fired by triggers, eg. 'after insert'. The trigger is run by the database process/thread and the actions it can take are database-specific. It could, for instance, call a C or java procedure that signals a named semaphore upon which you emailer is waiting or exec. an emailer app directly. Look at 'trigger' or 'create trigger' for your database.

Martin James
  • 24,453
  • 3
  • 36
  • 60