Fastest way to Update Dataset to Remote Server

[A.Russell]

I am developing a .NET 4.0 application that connects to a remote MS SQL database.

Calling tableAdapter.Update() on each table is very slow. From what I have read on the Internet, updating the database within a transaction should be much faster, however all my attempts so far have resulted in errors.

I am looking for the simplest and fastest method to update changes in a dataSet to the remote server -though anything that works would be very much appreciated



What I have tried:


using (TransactionScope tsc = new TransactionScope())
{
projectFamilyFrequenciesTableAdapter.Connection.Open();
projectCorpusCoverageTableAdapter.Update(dataSet1);
projectCorpusCoverageTableAdapter.Connection.Close();

textFamilyFrequenciesTableAdapter.Connection.Open();
textFamilyFrequenciesTableAdapter.Update(dataSet1);
textFamilyFrequenciesTableAdapter.Connection.Close();

//...and so on for each TableAdapter

tsc.Complete();
}



After processing several tables this throws the exception: "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."

I don't want users to need to alter their settings just to use my application, so this is out.

Placing only one update per transaction scope to avoid promotion to DTC results in another error:


using (TransactionScope tsc = new TransactionScope())
{
projectCorpusCoverageTableAdapter.Update(dataSet1);

tsc.Complete();
}
//... and so on for each table adapter



On some tables, this throws: "The transaction was aborted" or "The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements."

I have also tried the approach described here: http://weblogs.asp.net/ryanw/archive...commentmessage

I am not even sure I am on the right track. What is the fastest way to update a DataSet to a remote server?

[TechGnome]

The reason it's slow is because you're opening and closing the connection... open it manually ONCE... set the connection in the adaptor to the connection you just opened... call the update... then move on to the next adaptor, setting it's connection to the SAME connection you created & jsut used (do NOT close it) ... and update that datatable... and so on... when you are done THEN close the connection.

-tg

[A.Russell]

Thanks for the reply, TechGnome. I will try your suggestion and post back.

I was opening and closing it like that to try to prevent the transaction from being promoted to DTC, though as you can see from my error report, it didn't work.

Just doing an update on each tableAdapter (tableAdapter.Update(). without wrapping in a transaction takes as much as one minute per 100 rows.

I have been unable to successfully wrap all of my updates in a transaction to compare performance. I found a post that said wrapping in a transaction was nine times faster.

[A.Russell]

I switched to using TableAdapterManager.UpdateAll(), which wraps the changes in a transaction. However it is still very slow.

490 rows took 3 minutes, 4 seconds to update.

Is there anything else I can do by code to speed it up?

DateTime st = DateTime.Now;

tableAdapterManager.BackupDataSetBeforeUpdate = true;
tableAdapterManager.CorpusTableAdapter = corpusTableAdapter;
tableAdapterManager.ProjectsTableAdapter = projectsTableAdapter;
// + 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");

[vuyiswam]

I normally don't use Wizards to do my Database work , because when they are troubled you don't know where to start to look for bottle necks.

I see your code and TechGnome wrote a good point . You open the connection everywhere and that is a performance hit and again the database that you are Updating is it normalized. Take the Update statement that is being Generated by the Adapter and run it against the "Query Execution Plan" and see how long it takes to run and if its faster then you now know your database is cool and go to your code and look for bottlenecks. I have never heard that the Transactions are faster, well i normally don't use the because of the nature of the applications i write. looking at your code