0

I have a database with data that "expires" in a way -- after 10 days, the status changes from "Completed" to "Archived" and after 30 days rows still marked as "Assigned" get unassigned, for various reasons.

I'm trying to figure out the best way of handling this. (Affected row count each day would be in the thousands.)

My two routes that I have so far are:

  1. Clean the data whenever it's queried. This would work and run in pretty close to real-time, but it would add overhead to each query -- basically, we'd query, clean up the data, then run the normal query. That seems pretty inefficient for something that really only needs to be done once per day.

  2. Run a separate process. Like a Windows service or something, that cleans everything up. It would run once per day at midnight (which is when the app is quiet). It's not real-time, but we're talking a granularity of a day so that's probably fine. And we could add more tasks to the process as time goes on.

My question is: are these really the two main ways of accomplishing this, or is there another method that I'm not thinking of? Route #2 seems the clearly better one for my purposes, but I had a coworker say that it sounds like a hack to him so I thought I'd throw this out here and see if anyone had a better idea.

I suppose I could use database processes directly on SQL Server, but I don't know if that's really materially different from a separate process (both involve writing a new routine and scheduling it).

2

Those two categories you described are usually called synchronous transformation vs. asynchronous transformation. You find these terms often - but not exclusively - in the context of ETL processes and data warehouses (like here for MS SQL). So yes, at this level of abstraction, there are simply two categories. And both are well known standard approaches, each one with its pros and cons, there is no "hack" in this design.

However, there are lots of variants how to implement those strategies, and even a mixture is possible. In your case, the described implementation of option #1 sounds like a hack to me - at least the way I understand it - since it suggests to interfer with any query to the database, which I guess is quite impractical. A cleaner way of implementing this is probably by adding views to your data model which derive the status flags from the timestamp of the data "on-the-fly". That would probably make a real column for the status obsolete.

I guess that for your requirements a nightly process will most probably the cleaner solution. The event which triggers an expiring - from the business point of view - is the clock which reaches the point in time where the data reaches a certain age. So using this clock to trigger an event which switches some status flags is actually just a straightforward implementation of that business requirement.

The only drawback is that the status of a few records is not switched immediately at midnight, but maybe a few seconds or minutes later, depending on the latency of the system. So in theory, there could be a query asking for the status at 12:00:01am which gets a "wrong" result because the update process at night just finishes at 12:00:30am. But I assume that will not be a real problem for your case.

1

This can be categorized under Housekeeping (https://en.m.wikipedia.org/wiki/Housekeeping_(computing)). Given the actual retainment periods for old records, a daily cleanup/archival job at a time where there's little activity is a very reasonable choice, so your option 2 should be the way to go.

Although this option requires an additional scheduled job to be set up, I don't think it's a hack. You need to do the cleanup somewhere, and burdening unrelated client requests with cleanup tasks feels actually more hackish.

0

This is a very common problem.

The least-cost approach is to simply schedule a job to clean up the old records. Some database platforms offer a service just for this sort of work, e.g. SQL Server Agent. In most cases you won't need anything fancier than that.

If your solution requires precise timing, it can be achieved by differently structuring the data. Here's one way to do it in your case:

  1. Add a couple DateTime columns DateCompleted andDateAssigned.
  2. Modify your data retrieval to check these columns and exclude any rows that should be archived or unassigned.

    --To get a list of completed records which aren't archived yet
    SELECT * FROM MyTable WHERE DateCompleted >= DATEADD(DAY, -30, GETDATE())
    
    --To get a list of assigned records whose assignments have not expired
    SELECT * FROM MyTable WHERE DateAssigned >= DATEADD(DAY, -90, GETDATE())
    

In many cases you'd combine to two techniques to keep things tidy and efficient while retaining exact precision when handling the data.

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.