Schedule .exe execution based on SQL table timestamps

Question

Problem

I have a SQL table of message records, tbl_message_queue, that each have a release timestamp field (now or in the future). New records are constantly being inserted.

When release timestamp(s) come of age, the eligible records need to be processed by a console app .exe. Upon process completion, respective records will be removed from tbl_message_queue.

Lag time between release timestamp an execution should be reduced/eliminated.

Option #1: Frequent polling

I could set my .exe to run every 10-15 seconds via Windows Task Scheduler. Which would include a SQL statement like:

SELECT *
FROM tbl_message_queue
WHERE qmsg_ts_release <= GETDATE()

This feels like an inefficient, brute force, lag-friendly solution. But it's a structure I'm most familiar with.

Option #2: Re-scheduled SQL agent job

I've never tried this, but seems like it could work. It sounds really hack-y, so please forgive me if this is a really bad idea.

I could create a SQL agent job that has a CmdExec Job Step, and continually reschedule the job on:

1. UPDATE qmsg_ts_release
2. INSERT tbl_message_queue
3. Completion of .exe

to run next at MIN(qmsg_ts_release).

Phew... I know that sounds really ugly, but it's not aggressively polling, and would probably eliminate any lag.

Other options

Are there any other options or better solutions for this type of problem? I'm not familiar with best-practices in message queue architectures.

Would something like MSMQ or SQL Server Service Broker work better for this type of problem? I have very little familiarity with other options and am not sure where to start.

Answer 1

Your Option 2 should work, but you may get unexpected problems, because quite likely that frequent rescheduling is not a usual usage of SQL agent and you have little control over it.

Depending on your infrastructure you may implement a similar approach yourself. I will outline the algorithm.

To start with, imagine that your table with the queue is populated and is not changed by the external process.

Your console application would not start and stop, but would run always. Right after it starts it runs the query to determine the message that is at the top of the queue:

SELECT TOP(1) *
FROM tbl_message_queue
ORDER BY qmsg_ts_release

The application checks the timestamp of the returned message to see if it is in the past or in the future. If it is in the past, it should be processed immediately, the message is "overdue". If there was an overdue message, process it, remove it from the queue, then repeat the query and look for the next message in queue that may be overdue as well, and so on.

Once all overdue messages are processed the loop stops. At this point you have a timestamp of the message in the future. So, we just need to wait till that moment in the future. Send the thread to sleep for this period of time. Once it wakes up you know that it is the correct moment to process the message. Then just repeat the whole process.

This approach works reasonably well if the clock on the computer is stable and the possible wait time is not too long. All clocks and timers drift and the longer you wait, the more would be the accumulated error.

The next thing to think about is what to do when a new item is added to the queue. The process/procedure that adds an item to the queue needs a method to notify the processing application that it may need to wake up earlier than expected. But first, do a simple check. When adding an item to the queue you can check if there is any item already in the queue that has a smaller timestamp. If there is such an item already, you can simply add the new item to the queue and the processing application would get to it as usual. If the check shows that the new item has a timestamp earlier than all other timestamps already in the queue, it means that processing application is sleeping and we have to wake it up earlier than it expected.

Unfortunately, I don't know what would be a good method to send a signal to external application from, say, stored procedure. I would like to know one myself. In your case it is enough to simply restart the processing application whenever such item is added to the queue. As the processing application restarts it re-reads the queue and continues waiting accordingly.

In general, the process/application that adds an item to the queue should be able to notify the worker application of the change.

I just found another question that says that you can use SqlDependency to receive such notifications.