In my application, a record inserted into sql table has to be deleted after 10 seconds of time. Is it better to do it in c# code with delay or can i do it in stored procedure with delay?
And in my case, the traffic is high, like there are thousands of records gets inserted per second into db, which needs to be handled in the above way(each record should be deleted exactly after 10 secs after it is inserted). If i use delay using stored procedure, would there impact on performance?
I would use a stored proc if it's possible that the user can shut down the program before the 10 seconds is up for C# to do the delete. Plus, things like this are data integrity oriented, and most people agree that things like that should stay in the database (most of the time).
as long as you have an index built on the date field, the delete should be very fast, shouldn't have a performance hit unless you are locking the table or something
I would use a job to delete the records that are 10 seconds older (don't like the word delay, sounds like the thread is there, waiting)
I would consider avoiding doing this as a delay per record in either a stored proc or C#. Instead, have a job that runs periodically and deletes all records over 10 seconds old. Depending on how strict your needs are, you can schedule the job to run every second or every few seconds.
Another thing to consider: if the goal is to avoid showing records older than 10 seconds, then put that limit in your queries and don't select anything over 10 seconds. You could also use a view to only show records <= 10 seconds old. Then schedule the job to run less frequently or only at times of low activity.
