none
Update to Remote Server Very Slow Using TableAdapterMangager.Update() -How to Improve? RRS feed

  • Question

  • I am using tableAdpaterManager.UpdateAll(dataSet) to update to a remote MS SQL Server from a .NET 4.0 Window Forms application.

    It is very slow to make updates to the server. An example:

    490 row changes took 3 minutes, 4 seconds to update.

     

    I am currently in Thailand, where the bandwidth is appalling, and the server is in South Korea. However, an associate in Japan also reports very slow performance, so it can't just be bandwidth.

     What causes updates to be so slow, and is there anything that can be done in code to speed it up?

     

          DateTime st = DateTime.Now;
    
          tableAdapterManager.BackupDataSetBeforeUpdate = true;
          tableAdapterManager.CorpusTableAdapter = corpusTableAdapter;
          //... plus nine more table adapters...
          int test = tableAdapterManager.UpdateAll(dataSet1);
    
          TimeSpan et = DateTime.Now - st;
    
          MessageBoxEx.Show("Updating " + test.ToString() + " rows took " + et.Minutes.ToString() + " minutes " + et.Seconds.ToString() + " seconds");
            

    Saturday, October 2, 2010 1:41 PM

Answers

All replies

  • That's because you get a roundtrip for every row. Try changing DbDataAdapter.UpdateBatchSize property

    http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.updatebatchsize.aspx

    or go with a n-tier application.


    Miha Markic [MVP C#] http://blog.rthand.com
    • Marked as answer by liurong luo Thursday, October 7, 2010 9:32 AM
    Monday, October 4, 2010 6:56 PM
  • Thanks Miha,

     

    That certainly made an improvement:

     

    On each table:

            corpusTableAdapter.Adapter.UpdateBatchSize = 0;
            corpusTableAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
            corpusTableAdapter.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
            corpusTableAdapter.Adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

    Updating 12,567 rows took 5 minutes, 34 seconds. About 38 rows per second vs less than 3 per second before.

    It still makes for a coffee break, though. Is there anything else I can do that would significantly speed it up? Do stored procedures make a great difference? Relaxing constraints on tables?

     

    Tuesday, October 5, 2010 6:29 PM