How to get acceptable performance saving data tables to database using table adapter Update (update in batches)?

user_1174024 0
Viewed 43

I am having some performance issues updating a large datatable to SQL server using the table adapter functionality.

I am doing exactly what this examples is doing:

https://docs.microsoft.com/en-us/visualstudio/data-tools/update-data-by-using-a-tableadapter?source=recommendations&view=vs-2022&tabs=csharp

I am making 10,000 or so records in a datatable. And I'm ready to persist it to the database.

So I run:

    this.customersTableAdapter.Update(this.northwindDataSet.Customers);

It is very slow.

Do I need to use SqlBulkInsert? It does appear to perform much better.

Or am I just doing it wrong? Kind of seems like a strange thing to have this method available if it's just insanely slow to anyone who uses it. I prefer to use this .Update method because its error messages are much more readable. But perhaps it's behind the scenes inserting 1-record-at-a-time.

sqlbulkcopy datatables sql-server c#
1 Answers

I found this: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/performing-batch-operations-using-dataadapters

It does appear to make it faster.

MyDataSetTableAdapters.TableAdapterManager tableAdapterManager = new MyDataSetTableAdapters.TableAdapterManager();
tableAdapterManager.Connection = sqlConnection;
tableAdapterManager.MyDataTableAdapter = new MyDataSetTableAdapters.MyDataTableAdapter();
tableAdapterManager.MyDataTableAdapter.Connection = sqlConnection;

Console.WriteLine("Saving data set " + MyDataSet.MyData.TableName);

tableAdapterManager.MyDataTableAdapter.Adapter.UpdateBatchSize = 1000;
tableAdapterManager.MyDataTableAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
tableAdapterManager.MyDataTableAdapter.Adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
tableAdapterManager.MyDataTableAdapter.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
tableAdapterManager.MyDataTableAdapter.Adapter.Update(MyDataSet.MyData);
user_11740240
Related