Fulfilling Verus Database Queries Storing Items in a Collection?

Question

I am trying to make a reminder system, and I use quartz for my planning. However, I came up with a couple of possible ways to do what I need to do, but I'm not sure what the best way is and how to test it.

Basically, I have a reminder system in which users can set reminders. This is similar to Google Calendar. You set the date and time of your event, and then you set a reminder saying "remind me 15 minutes before"

So, you could host the event on May 10, 2011, 9:59, and you could remind me “15 minutes before”

So it will be May 10, 10:44.

I will be in a hosted environment. (My site and planning will work in the same environment and even in the same solution. Therefore, it cannot significantly slow down users browsing my site.)

I also use nhibernate and free nhibernate to execute a db request. I am using asp.net mvc 3 for my website.

Option 1.

Make a database request every minute and get all reminders that should be sent this minute. This, of course, would mean a database query every minute and probably too intense for the general environment.

Option 2

Run a database query every 5 minutes and grab all the reminders that should be sent to this 5 minute block and store them in a collection (so that memory), and then check every minute which ones should be sent.

This, of course, reduces the number of queries made, but I'm not sure that it will be extremely intense.

Option 3

Same as Option 2, but send a request every 15 minutes and save it in the collection.

This, of course, means much less database queries, but more is stored in memory.

Option 4

Run a database query every 15 minutes and get all reminders in this block and run them immediately.

This means that they will not be stored in memory for a very long time and reduce the number of requests. However, depending on when the user is set up for the reminder, the email may be sent earlier than they were set.

For example, they said to remind me at 10:44. I would plan for my planner to start at 10:00, and it would snap from 10:00 to 10:15, and then from 10:15 to 10:30, and then from 10:30 to 10:45.

So the email will actually arrive 14 minutes earlier than anticipated.

Answer 1

This is how I would solve this problem.

• In the DB kernel, I would create a simple queue. This message list will also include sending time. When prompted, this list will have the next item at the top.

• The message agent will request this list and act on the top item or sleep until the top item in the list appears.

One of the advantages of this method is that you do not have a live agent applying business rules when it checks the queue. If you want it to wake up every minute (for example, to check if there are new messages that need to be sent), you just make sure that this queue always has an event every minute (this event can be of a type that does not send a message, a message "wake up" has no goals). The agent wakes up and checks. Then, if you want to apply more complex planning rules, they are simple. You do not need to transcode the agent, you just need to change which messages are queued. (For example, check every 10 minutes when the system is in high use, and every 20 minutes when it is underused and stops checking during an overnight backup). This can be done (and modified) without changing your agent code.

---

A simple example of the real world

QueueTable ---------- ID int deliverTime datetime nagCount int expireTime datetime active bool processed datetime (null) ' maybe some audit stuf... ' content of the message -- or external link ' etc 

START: the agent makes a call like this

 SELECT TOP 1 * FROM QueueTable WHERE active = true and processed is null ORDER BY deliverTime DESC 

Then the agent looks at what the delivery time is:

• If it passed or in the following fuzzy border (1 second?), It sends a message, then sets the processing at the current time in db and returns to START:

• If in the future he will sleep before this delivery time or sets an event to wake him at this time (depending on the platform).

I was initially treated as logical, but if you use zero equal to not processed, it can double as an audit field.

---

An example to check every 10 minutes no matter what.

How it works:. Since the results are sorted by time, the fastest will be displayed at the top. What we do is add an item 10 minutes from the result set. Thus, the top element will never be more than 10 minutes from the current time.

 SELECT TOP 1 * FROM QueueTable WHERE active = true and processed is null UNION ALL SELECT NULL, DATEADD(min,GETDATE(),10), null, null, false, null, ... ORDER BY deliverTime DESC 

Note. The active column is used as a flag here to indicate that no action will be performed. This entry is just a marker to wake up the agent. This method also regulates this depending on other rules (for example, time of day, because at night you do not need to check often, etc.).

Answer 2

I would choose option 4 immediately if it does not meet your requirements.

Other options really depend on your system profile (How many people use it? How many reminders are there in any given period of 5/15 minutes?) These are questions that you need to answer. Also, how much activity is already happening on the server? If it is not yet under high voltage, then the request every minute is not so much.

Finally, keep in mind that if you request only every 5/15 minutes, you can skip changing / adding / deleting to the schedule if this happens after you requested, and the reminder should drop in that 5/15 minutes window. Again, this comes down to the requirements of the applications as to whether it is acceptable or not.