Overwrite constantly updating data in a SQL table?

[Drake]

Hey all,

I have a C# program that's storing info in a MSSQL database. The program queries a bunch of camera and sensor pods for environmental info approximately every two minutes and stores it in a table. I only need about two weeks' worth of info.

I'm wondering if it's possible to have the oldest row in the table be overwritten once enough data has been collected. Initially I thought that I would have the program count the number of writes in a week (~5,040), copy the table to a new table (last_week), and begin filling in the new table, but it seems a much more optimal solution would exist. Is it possible to have a SQL table operate with this tape-like functionality, or would that have to be written into the program writing to the table?

 

[maw]

assuming your table has a "date" field:

DELETE FROM table WHERE date < somedate

 

[Spetsdod]

assuming your table has a "date" field:

DELETE FROM table WHERE date < somedate

I do something almost exactly like this with almost exactly the same kind of data. I just have a scheduled task that runs once a day that does a DELETE on data older than my age threshold. Works perfectly.

edit:
mssql_query("DELETE FROM images WHERE DATEDIFF(day,imageDate,getDate()) > $maxAge");

 

[tim_m]

you could create an after insert trigger to execute the delete query, but depending on the rate of inserts you're doing, it may be better to go with a separate scheduled task that only has to run once a day or whatever.

 

[PTNL]

Both the scheduled SQL job and trigger approach will do the job.

My question is this: Should you delete the data, or rather move it to an 'archive' table or database? Just thinking that it might be useful for comparative data of a recent two week interval versus an old two week interval. The info on what kind of an impact this would have on table row count and database file size is unknown, but this might be worth considering for future needs.

 

[mikeblas]

If you DELETE the oldest rows as suggested, you'll find that you're doing one delete for each insert. Perhaps performance isn't of much concern, but you might find it easier to just keep writing, limit your selects to only the newest rows, and delete batches of rows less frequently.

There's 20,160 minutes in a week, and that means you'll have 10,080 rows per camera in two weeks. This isn't a lot of data at all, unless you have more than ten thousand cameras. What problem is it that you're specifically trying to solve that you're hesitant to apply the obvious solution?

 

[ameoba]

This isn't a lot of data at all, unless you have more than ten thousand cameras. What problem is it that you're specifically trying to solve that you're hesitant to apply the obvious solution?

Some people think a 100MB table is obscenely large. Without proper indexes, I guess it would be.