CODEWITHSUNDEEP
c#sqlperformance
Hamid
Hamid

Reputation: 617

how to fast Insert 4,500,000 record into sql server database with c#

I have a program in c# that Insert 4,500,000 record into sql using ExecuteNoneQuery and take too long to insert I need a fast way to insert that take maximum about 10 minutes however when I insert 4,500,000 record from another table to my table via management studio it take 3 minutes

Upvotes: 0

Views: 1422

Answers (2)

dsz
dsz

Reputation: 5202

As Oded has already stated, the starting point for this is SqlBulkCopy. However, if you have control over the database, you should also check that the Recovery Model on the database is set to Simply or "Bulk Logged". Without this you will take a heavy hit with SQL Server creating journal entries. You also need to ensure that SqlBulkCopyOptions of TableLock is set.

These two are straight forward. It can also be worth playing with the SqlBulCopy BatchSize setting, and on the transaction model (see UseInternalTransaction) but these settings are harder to give advice on, as the optimal settings can be quite different for different scenarions. If doing the TableLock and checking the Recovery Model doesn't quite get you the speed you want, then you can start playing with the Batch Size, but it is more of a grey area.

Upvotes: 1

Oded
Oded

Reputation: 499002

The SqlBulkCopy class is designed for fast insertion of large sets of data.

However, you need to understand that with that kind of size, disk access speed and network latency/bandwidth/saturation come into play.

Your example of populating one table from another is not valid in such a scenario as you are copying on the same machine.

Upvotes: 5

Related Questions