Alternative to MSSQL table polling

Question

I have an MSSQL table that contains scheduled tasks that my Windows service should process based on a timestamp, and I was wondering what alternatives I need to poll a table like this.

SELECT * FROM mydb WHERE SYSUTCDATE() >= timestamp 

I will probably have to poll the table at least every 5 seconds. Basically, I want my Windows service to process the data at the point in time set by the timestamp in the table.

For me, this does not seem to be the most effective way. I have already studied the DML and CLR triggers, and I don’t think they will work, because they will fire when the data changes, and not when the timestamp has passed. Thoughts?

---

Update 2:

I realized that calling it “planned tasks” was a poor choice of language, so I’ll try to describe it in more detail.

The goal of this project is to send notifications by phone to people depending on our business logic. One of the scenarios is that several people must call at a specific time based on an internal event. One and the same person can be called several times depending on the answer to a phone call. Thus, in order to simplify things and eliminate the complexity and overhead of managing the status of each phone call, I thought it would be nice to call each phone call ahead of schedule, indicating it as an entry in the table. When notifications must be stopped, pending phone calls are removed from the table. This will simplify the design of the Windows service. All he would do was send a notification based on his timestamp in the table.

---

Update 1:

Message queue

I did not understand how the sender will send messages to the queue at the right time.

SqlDependency

I am having trouble using the sample code from Change Detection using SqlDependency . For some reason, the OnChange event only fires initially, nothing happens later.

Update: I do not think that SqlDependency will work, since the data in the table will not change to create a trigger fire.

 void Initialization() { // Create a dependency connection. SqlDependency.Start(connectionString, queueName); } void SomeMethod() { // Assume connection is an open SqlConnection. // Create a new SqlCommand object. using (SqlCommand command=new SqlCommand( "SELECT timestamp,othercolumn FROM mydb WHERE SYSUTCDATE() >= timestamp", connection)) { // Create a dependency and associate it with the SqlCommand. SqlDependency dependency=new SqlDependency(command); // Maintain the refence in a class member. // Subscribe to the SqlDependency event. dependency.OnChange += new OnChangeEventHandler(OnDependencyChange); // Execute the command. using (SqlDataReader reader = command.ExecuteReader()) { // Process the DataReader. } } 

Answer 1

OK, first of all, think about not doing this at all. Disabling all the useful work with periodic tasks that are configured in the database is a fragile design that breaks easily when someone improperly adjusts the situation (easy to do, because you need fairly advanced triggers to check if the schedule is consistent), and also tends to create an incomprehensible system when there really are hidden dependencies in tasks (if A did not run for some time before B, interruptions, something like that). Source: personal experience with three such systems in three different companies and three different platforms / technologies, and somehow they all suffered from the same problems, so, apparently, this is something. Think, just write out what you want to plan, like plain old code, with a configuration file. Sure, this will not be so general, but the people who need to support it will be grateful to you, especially because their needs are becoming more complex.

SqlDependency quite volatile and not very usable, even if you have a supported query. In your case, as you noticed, this does not work, because the database engine will not publish a notification if the data does not actually change - it does not matter that the results of the query change over time. As Nick noted, polling a database every 5 seconds is usually just fine. This creates a small load, provided that you create an index on mydb.timestamp (and create it, this is very important, because doing a table scan every 5 seconds is not OK).

The only objection is latency: if any schedule updates should be performed earlier than once every 5 seconds, the survey is not good enough. In this case, you can use Service Broker and send a notification to the queue that something has changed (possibly from a trigger). In fact, SqlDependency uses the same approach under covers, so you can create a SELECT * FROM table dependency to be notified when nothing changes in the table, and then execute the actual query to get what you need (possibly not finding anything) . Beware, however: getting the code for this correctly without getting confused by a few quick updates, or disconnecting is not trivial and probably not worth it, and not just restarting periodically.