locked
Linq SubmitChanges Performance RRS feed

  • 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

    Thursday, May 7, 2009 8:31 AM

Answers

  • 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
    • Marked as answer by Harry Zhu Thursday, May 14, 2009 4:27 AM
    Friday, May 8, 2009 3:55 AM
  • 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
    • Marked as answer by Harry Zhu Thursday, May 14, 2009 4:27 AM
    Saturday, May 9, 2009 12:44 AM

All replies

  • 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
    • Marked as answer by Harry Zhu Thursday, May 14, 2009 4:27 AM
    Friday, May 8, 2009 3:55 AM
  • 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
    • Marked as answer by Harry Zhu Thursday, May 14, 2009 4:27 AM
    Saturday, May 9, 2009 12:44 AM