CODEWITHSUNDEEP
linqlinq-to-sqloptimization
Eilev
Eilev

Reputation: 273

LINQ DataContext persistance best practice for many inserts

I have a logger class that is used all across my application, both in requests and threads. The logger consists of a database insert for each Log.Insert(string text). The logger is a static class, and a new database context is created for each call to Insert. There might be many calls to Insert, and a lot of database contexts simultaneously is not good for optimization. (I sometimes get database timeout, because of too many sql operations across the application - so optimization is indeed needed).

Can this be optimized by creating and storing a single database context in a static member of the Logger class, that is only used for the Log.Inserts? Or will this fail?

Simplified version of the logger class;

[Table]
public class Log
{
   [Column]
   public string Text { get; set; }

   private static DataContext DatabaseContextInsert { get; set; }

   public static void Insert(string text)
   {
      if (DatabaseContextInsert == null)
      {
          DatabaseContextInsert = DataContextHelper.GetDataContext();            
      }

      var log = new Log { Text = text };          

      lock (DatabaseContextInsert)
      {
        DatabaseContextInsert.GetTable<Log>().InsertOnSubmit(log);
        DatabaseContextInsert.SubmitChanges();
      }
   }
}

Upvotes: 1

Views: 479

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062660

Using a static logger would be a very bad idea. In addition to synchronization issues, you would have problems with all the items being held in the data-context forever (a data-context likes to keep hold of objects it has seen).

You mention that you think that lots of data-contexts isn't good for optimisation, but the connection is presumably already pooling (it does that by default), so the data-context isn't actually very "heavy".

If the log entries do not need to be inserted synchronously, I would be tempted to do something like throwing new log items into a queue (synchronizing access), and having a worker thread that empties the queue every 10 seconds, essentially doing something like:

// adding an item
lock(queue) { queue.Enqueue(text); }


// worker code, every 10 seconds
List<string> items = new List<string>();
lock(queue) {
    while(queue.Count != 0) items.Add(items.Dequeue);
}
using(var ctx = CreateContext()) {
    foreach(var text in items) {
        ctx.Logs.InsertOnSubmit(new Log { Text = text });
    }
    ctx.SubmitChanges();
}

Then only one thread is writing to the database, and you have far fewer transactions.

If this isn't an option, and you need to do it synchronously, then frankly I'd drop down a few levels to something like "dapper", i.e.

using(var conn = CreateOpenConnection()) {
    conn.Execute("insert [Log]([Text]) values(@text)", new {text});
}

which removes the need for a data-context at all, is much simpler, and removes the transaction from consideration.

Upvotes: 2

Related Questions