可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

When it comes down to INSERTing large amount of data (say 100.000 rows) in an ARRAY object, which one do you think it would be faster?

Through Entity Framework? (call SaveChanges() when u have inserted 100.000 rows)
Looping every row (100.000 times) with an INSERT stored procedure?

If you could also provide a reference that would be very awesome.

Thanks

回答1:

Looping 100K times calling a stored procedure will at a minimum create 100K cross process and/or cross network calls will be slow.

If you're using SQL server, another option is to use TVPs (table value paramaters) to avoid calling insert in a loop from your C# code. It allows you to pass a table of data to a stored procedure in one call.

From the link above, they recommend 1000 rows at a time (but always measure and experiment for your app):

Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

So, maybe try out looping 100 times passing a 1000 rows at a time (instead of cross the boundary 100K times).

You might also want to re-evaluate why asp.net has 100K items at one time in your app. Is that passed up to the server and held in memory at once with possible memory issues? Can that be broken up? Are you doing data processing where asp.net is reading out and processing 100K rows where a sql server agent job might be more appropriate? If you provide more details on the data flow of your app and what it's doing, folks might be able to offer more options.

回答2:

The stored procedure will be faster. Entity Framework does not have batching, so you suffer the performance overhead of EF on top of hitting the database 100k times. Raw ado is going to be faster than an ORM for this kind of stuff.

Here is a link of some comparisons http://blog.staticvoid.co.nz/2012/03/entity-framework-comparative.html It does not have raw ado.net in the comparison but dapper is the closest you will see to speed to that.