CODEWITHSUNDEEP
c#sql-serverperformancedatatablesql-server-2012
Andrew
Andrew

Reputation: 7788

Efficient bulk SQL database table update based on the datatable

I have over 1 million rows that I check for changes and then update. I completed my program that goes over each record and then updates the database. But this operation takes couple hours to complete with multithreading. I have optimized queries, inserts and checks to minimize the database load. I achieved much better results, but it is very slow.

Is there any way to maintain a DataTable with correct records in memory, and then upload whole data structure as 'virtual table' to SQL server in one update and let SQL server handle the updates?

I have seen similar in the past, it was done via function on Posrgre server (without involving C#). I need my program to be done in of minutes, not couple of hours.

Upvotes: 0

Views: 2453

Answers (2)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

Either insert your new data in a temp table with Bulk Copy or use a table valued parameter (TVP), then use the SQL MERGE command to update the rows in the existing table.

Upvotes: 2

mutex
mutex

Reputation: 7738

Have you looked at the SqlBulkCopy Class?

System.Data.SqlClient.SqlBulkCopy

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Upvotes: 2

Related Questions