Linq SubmitChanges Performance

Question

Hello,

I'm pretty new to LinqToSql, I like it but, in the following code I'm facing a very bad performance problem. (Here also the debug output.)

MyList is filled with few simple Test elements ( only 31 ) , the database is simple 5 tables with some relationships rappresented in the Test entity.

The problem is that the SubmitChanges is too slow (more than 1 minute for 31 elements), is there a way to speed up the submit? Or I'm doing somethings wrong?

Thanks

Regards

Max

 

Stopwatch stp = new Stopwatch ();

stp.Reset();

stp.Start();

 

var qrySource = db.Tests.Select(x => x);

 

stp.Stop();

Debug. EventLogger .DebugTrace( "DatabaseUpgrade qrySource: " + stp.ElapsedMilliseconds + " ms" );

stp.Reset();

stp.Start();

 

List < Test > myList = qrySource.ToList();

 

List < Test > tmpList = new List < Test >();

 

foreach ( var item in myList )

{

Test tmpTest = Clone(item);

Application .DoEvents();

tmpList.Add(tmpTest);

}

 

stp.Stop();

Debug. EventLogger .DebugTrace( "DatabaseUpgrade copylist: " + stp.ElapsedMilliseconds + " ms" );

stp.Reset();

stp.Start();

 

Application .DoEvents();

dbtmp.Tests.InsertAllOnSubmit(tmpList);

 

stp.Stop();

Debug. EventLogger .DebugTrace( "DatabaseUpgrade InsertAllOnSubmit: " + stp.ElapsedMilliseconds + " ms" );

stp.Reset();

stp.Start();

 

dbtmp.SubmitChanges( ConflictMode .FailOnFirstConflict);

 

stp.Stop();

Debug. EventLogger .DebugTrace( "DatabaseUpgrade SubmitChanges: " + stp.ElapsedMilliseconds + " ms" );

 

 

The debug output:

 

DatabaseUpgrade qrySource: 1 ms

DatabaseUpgrade copylist: 168 ms

DatabaseUpgrade InsertAllOnSubmit: 7 ms

DatabaseUpgrade SubmitChanges: 67486 ms

Answer 1

That makes no sense. Could something else be locking the table you're inserting into? Or something else locking something that the table you're inserting into is referencing (w FKs)?

Run a trace with SQL profiler to see what is going on...

---

Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools

Answer 2

Those calls to Application.DoEvents() will get you into trouble in a production application.

Instead, consider using the BackgroundWorker class for this.

Joe

---

Write LINQ queries interactively - www.linqpad.net