CODEWITHSUNDEEP
sql-serverperformancebulkinsert
venkatesh
venkatesh

Reputation: 111

LARGE Parallel insert into SQL server table

We have table with Identity column and it is the clustered index.

When trying to insert a large number of rows around 400 Million for running analytics it is taking 2-3 hrs only for inserting.

The insert happens from .net application directly where 8 cores write parallelly into the table.

we tried few methods trace(610) which helped around 5% and also disable lock escalation which havnt helped much.

Can we improve the time taken? Also are there any other factors which I can check and verify?

Upvotes: 1

Views: 807

Answers (1)

edmarisov
edmarisov

Reputation: 148

400 M per 3 hours means 40000 inserts per second which is already sounds good for one application running on 8 core machine. Have you tried running more instances of this app from different machines? Does it helps? If yes then just scale it, if no then i would check for all constraints on db (foreign keys, indexes, primary keys, triggers) and if possible to remove/optimize them. Also i guess that you are using some sort of connection pool to db in your app and batch inserts - try to experiment with different sizes of batch and pool. Also what about resource consumption on db machine (especially I/O is important in this case)? If machine cannot handle that rate of requests - consider scale up or hw upgrade.

Upvotes: 1

Related Questions