Caching for later write to database in a asp.net webservice?

Question

I'm writing a web service which, for each method call, must write a log entry into database. At a specific time, the call to this method may be very intensive (up to 1-2k request/minute), and our server is not so strong.

I want to use a List<Log> to cache the log entries, and then:

• batch insert 30-40 rows to the database, which greatly reduces the overhead
• when no more requests in more than 30 secs or 1 minute, all of the remaining cache will be written to the database.

The first condition is OK, but I don't know how to implement for the second condition.

How can I do this, without adding too much overhead to my web service?

EDIT: I solved this based on Wheat's suggestion.

• For each log entry, I send it directly to MSMQ queue and forget about it
• An separated service run continuously, take all of log entries are currently in the queue, bulk insert them to database, and then sleep for 30 seconds.

MSMQ was very helpful in this case!

Answer 1

You could use MSMQ or SQL Server Service Broker.

Answer 2

It sounds like you want to trigger a task on an interval of some kind (30-60 seconds.) The trick is to have ASP.NET open and iterate through the cache at your desired interval (30 or 60 seconds) without having an incoming web request trigger this. Some discussion on this article: Easy Background Tasks With ASP.NET.

I'll suggest 2 options for storage with this approach:

1.. For an ASP.NET and IIS only solution, you could write to the ASP.NET Application Cache.

List<Log> myLogs = new List<Log>();
myLogs.Add(new Log{ Text = "foo"});
Cache["MyLogs"] = myLogs;

Consider this problem, though. The ASP.NET Application Cache isn't durable storage. What happens when your application dies, or IIS is reset, or the machine loses power? Do you care about what was in cache?

2.. Embed a SQL Compact database in your application. Write your Logs there. Combine this storage mechanism with the Easy Background Tasks With ASP.NET.

IMO, I'd fully go with MSMQ or SQL Server Service Broker option suggested in another answer. This guarantees durability for you. Hopefully you've got full control of that web server to leverage these components. One requires Windows components to be installed/enabled/secured, and the other is a SQL Server specific feature.