0

I am working on an enterprise level application. I have an event table in the database. I need to update my events' states in the database on a precise date and time. There may be 200 milliseconds delays max. Since my event table will grow over time, I think long-polling may be a performance issue. In my application, which is net.core web API, I want to build a structure that accepts ScheduledTask and somehow sleeps until next TimeToExecute time to come.

public class ScheduledTask
{
    public string CurrentState {get; set;}
    public string NextState {get; set;}
    public DateTime TimeToExecute {get; set;}
}

So to overcome my situation I would use;

  1. IHostedService; With this approach, I need no 3rd party tool. I can inject the IHostedService and use it. I can check the list in every 150 ms and execute the update process asynchronously.

  2. HangFire; As I searched, this tool works just like the IHostedService more or less.

My question is as my event table grows and the number of events grows(like 500 per minute) do I have any other architecture options other than above options?

  • 3
    Your question was going well until you asked for a product/service recommendation. We don't do that here, and even if we did, you haven't provided enough information in your question to make an educated guess. Modify your question to provide more details about what you want to accomplish, and remove the recommendation request. – Robert Harvey Sep 25 '18 at 19:10
  • You also haven't defined your required level of precision, only that Hangfire won't work. – Dan Wilson Sep 25 '18 at 19:43
  • @RobertHarvey Thanks for the warning. I've tried to improve it according to your comment. Hope this one is suitable now. Please let me know if I still should alter my question – ilkerkaran Sep 25 '18 at 19:57
  • @DanWilson, I've edited the question, thank you for contributing my question. – ilkerkaran Sep 25 '18 at 19:58
  • Do I understand correctly that you know the new state and time it should take effect ahead of time and that you need queries to the database within 200 ms of that time to reflect the new state? – Blrfl Sep 25 '18 at 20:08
1

Trying to do this with any kind of lightweight process (asynchronous processes, threads or anything else like that) is a recipe for failure unless you're in an environment that gives you hard guarantees about when the code will be executed. It also doesn't help that if your program dies and is restarted, any pending status updates will be lost unless you have a way to reconstitute them and make updates that should have happened while the program wasn't running.

The advance notice you get of a new status and when it takes effect are data, and what better way to deal with it than to put it in the database? You were, after all, going to do a transaction to update the status anyway once your LWP decided it was time. Doing so gets you all of the ACIDity your database has to offer and will scale to whatever your database can store rather than the number of jobs the LWP environment you're using can muster.

Doing this requires that you break the status of your events out into a separate table that lists the time ranges and what status is in effect during each one.

This example is nominally PostgreSQL, but you can do the equivalent in any relational database:

CREATE TABLE event (
    id BIGSERIAL PRIMARY KEY,
    -- All other columns except the status
);

CREATE TABLE event_status (
    event BIGINT REFERENCES events(id),  -- Which event
    times TSTZRANGE,    -- Range of times this status is valid
    status INTEGER      -- The status (doesn't have to be INTEGER)
);

That's the table structure.* You'll need to set up constraints and triggers to make sure that rows in event_status for a given event express a contiguous range of times that covers the dawn of time to infinity and that insertion of a new row properly adjusts the end times of surrounding rows.

With that in place, getting the current status is a JOIN of the two tables that weeds out rows that are either not in effect anymore or not in effect yet. This works nicely in a view:

CREATE VIEW event_current_status AS
    SELECT event.*, event_status.status
    FROM event JOIN event_status ON event_status.event = event.id
    -- The && operator means containment, i.e., the current time
    -- falls within the range of the 'times' column.
    WHERE event_status.times && now()
    ;

Properly indexed (event and times in the same index on event_status should do), you should have no performance concerns.


* You mentioned in a comment that this information is available elsewhere in the database; I'm going to hand-wave that for now. That said, it might be a good idea to investigate the merits of pulling it directly from the tables where it originates instead of having a duplicate status table. There are trade-offs for both that will depend on your situation.

  • Personally, I prefer only ever storing the lower bound of this kind of time range, and deriving the upper bound (if it exists), since it will prevent gaps, but this works. Note that time-based ranges MUST use an exclusive upper-bound, or you run end up with overlap/gaps. – Clockwork-Muse Sep 25 '18 at 22:38
  • @Clockwork-Muse I've done it both ways. The execution plans with properly-indexed ranges tend to be a lot simpler because you can pull the one row you need directly without having to figure out which lower-bounded record is the right one. The extra queries to adjust the ranges only ever happen on insert, but if state changes or queries are infrequent enough, it might be a wash. – Blrfl Sep 26 '18 at 2:13
  • @Blrfl, if I understand correctly, you suggest managing the states in the database layer because all the related data and process only affects the database. Doesn't it count as a downside/bad practise splitting business logic? – ilkerkaran Sep 26 '18 at 7:11
  • @Progressive Let's just say I'm suggesting that you use your database (I'm assuming relational, which might be erroneous) to do the things it was born and bred to do instead of reinventing a lot of wheels in what's basically a giant race condition. When the system misses or loses state change, will "it's okay because we built this according to what someone else told us are best practices" be acceptable to the SVPs who are angry as hell because it impacted revenue? Also, see this related question. – Blrfl Sep 26 '18 at 11:54
  • @Blrfl - Oh, yeah, you definitely don't want to actually compute it at query time, especially at the scale the poster is talking about. I'm thinking something more along the lines of a MQT. – Clockwork-Muse Sep 26 '18 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.