SqlDataAdapter.Update() of 2 million records is extremely slow

Question

I have a customer that wants to import his sub-customers pricetools (more that 2.000.000 records) every day into a SQL Server database (and yeah....there are more than 900.000 rows of changes every day).

The data is provided in CSV format (not in RFC-4180 standard ç_ç, but nvm) and can be an Insert, Delete or Update of data.

My problem is that the insert of the data inside the database take more than 1 night to end and I need to speed it up.

What I'm doing at the moment is:

Cast the csv file into a Datatable (Tab1) (~3 minutes)
Select all data inside the previous table (Tab0) and match them with the Tab1 (~15 minutes, the unchanged rows are flagged as unmodified, so they are ignored in the adapter.Update, I check that thing for the first rows and seems that it works, I use dataRowToProcess.AcceptChanges() to achieve that).

Launch the following command to apply the changes (More than 5 hours for 900.000 changes):

cmdSQL = New SqlCommand(superQuery, cn)

Dim adapter As SqlDataAdapter = New SqlDataAdapter(cmdSQL)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim build As SqlCommandBuilder = New SqlCommandBuilder(adapter)
build.SetAllValues = False

adapter.Update(dataTableCustomersDetail) 'Insert/Update records

If I have many inserts the process, it is slower than the same amount of updates. What am I doing wrong? Am I missing some SqlDataAdapter option?

Thanks

With [SqlBulkCopy](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8) this should be a matter of seconds or minutes, depending on your hardware. — MatSnow, Apr 30 '19 at 09:26
Interesting Idea.... but no access to the source database is provided for security policy, so i need to use an Export (From 3rd Party)/Import process. Or i can open a Reader on a datatable? — Legion, Apr 30 '19 at 09:37
There's no access needed to the source database. You can use the DataTable that's already generated from the csv-file. As `SqlBulkCopy` can only insert but not update records, you'll need to insert into a temporary staging table and then insert/update the records. Here's already a similar thread on SO: https://stackoverflow.com/questions/4889123/any-way-to-sqlbulkcopy-insert-or-update-if-exists — MatSnow, Apr 30 '19 at 09:42
Now to insert in temporary table and do a merge data it takes less than 10 minutes... Never think that it can made all that difference!! Many thanks. — Legion, Apr 30 '19 at 13:03

score 0 · Accepted Answer · answered Apr 30 '19 at 14:23

Thanks to @matsnow i figuredOut a solution with SqlBulkCopy. Considering that half of the table change everitime and that is a static anag i decide that a Delete/Insert of the data is the fastest way to follow (Now it takes 5-6 Minutes instead of 10).

Code:

'Delete all table content
Dim cmd As SqlCommand = New SqlCommand("TRUNCATE TABLE " + tableName, cn)
cmd.ExecuteNonQuery()
'Insert all records
Using sbc As SqlBulkCopy = New SqlBulkCopy(cn)
    sbc.DestinationTableName = tableName
    sbc.BulkCopyTimeout = 1000
    For Each column As DataColumn In dataTableCustomersDetail.Columns
        sbc.ColumnMappings.Add(column.ToString(), column.ToString())
    Next
    sbc.WriteToServer(dataTableCustomersDetail)
End Using

score 0 · Answer 2 · answered Mar 23 '21 at 16:41

Use Connection.BeginTransaction() to speed up the DataAdapter update.

cn.Open() 'open connection
Dim myTrans As SQLTransaction
myTrans = cn.BeginTransaction() 
'Associate the transaction with the select command object of the DataAdapter
adapter.SelectCommand.Transaction = myTrans 

adapter.Update(dataTableCustomersDetail) 'do the update as before

Try
    myTrans.Commit()
Catch ex As Exception
    myTrans.Rollback()
End Try
cn.Close()

With 8000 rows this changes the update time from over 5 minutes to 2 seconds

SqlDataAdapter.Update() of 2 million records is extremely slow

2 Answers2