SQLBulkCopy vs DataAdapter for inserting/update 10lcs records

.net dataadapter performance sqlbulkcopy vb.net

Question

I have around 1,000,000+ records that I want to insert into the database.

Records having same Id should be updated else inserted

update/insert logic is written in Stored Proc

I'm using DataAdapter.Update method to do this work.

Here is the problem.

A DataTable takes not more than 65,000 records.

Insert/update is only 60 records per second.

How can I improve this performance, and can i use SqlBulkCopy here, can it do this Update/Insert together? what would be the performance in it

Popular Answer

Are you loading the records into a DataTable just for the insert update?

If so you would probably be better off just calling the SP directly one at time and skip the DataTable.

Or you could use TVP and IEnumerable SqlDataRecord and then you are not limited to 65000.

An example of using IEnumerable SqlDataRecord



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why