对于大量插入，多久调用一次 DataContext.SubmitChanges() ？
在调用 SubmitChanges 之前，我应该调用多少次 InsertOnSubmit？我正在添加来自 Web 服务的数据，该服务一次可以返回数万条记录。 Web 服务周围的包装类将记录公开为 IEnumberable 集合，以隐藏复杂的分块机制。
是否有关于在提交之前我应该积累多少插入内容的指南？
How many InsertOnSubmit should I call before calling SubmitChanges? I'm adding data from a web service that can return tens of thousands of records one record at a time. The wrapper class around the web service exposes the records a an IEnumberable collection to hide an elaborate chunking mechanism.
Are there guidelines on how many inserts I should accumulate before submitting them?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
对于批量更改 1000 条记录的一个字段与一次更改 1 条记录的情况：一次 1 条记录需要 0.1989 秒/记录，而批量需要 0.0711 秒/记录。因此，在我的测试中，Batch 的速度大约是原来的 3 倍。请注意，批量大小的效率有所不同。
commitchanges() 一次 1 条记录
记录秒秒/记录
1000 198.95 0.1989
批量submitchanges()
记录秒秒/记录变化百分比
100 12.20 0.1220 133%
200 18.33 0.0916 122%
500 37.59 0.0752 106%
1,000 71.07 0.0711 103%
2,000 138.64 0.0693 102%
10,000 680.36 0.0680
For making changes to one field for 1000 records in batch vs 1 at a time: 1 record at a time takes 0.1989 sec/record vs batch which takes 0.0711 sec/record. So Batch is about 3x as fast in my tests. Note that the batch sizes vary in efficiency.
submitchanges() 1 record at a time
records sec sec/record
1000 198.95 0.1989
Batch submitchanges()
records sec sec/record % change
100 12.20 0.1220 133%
200 18.33 0.0916 122%
500 37.59 0.0752 106%
1,000 71.07 0.0711 103%
2,000 138.64 0.0693 102%
10,000 680.36 0.0680
它还取决于您需要插入的数据类型。有时我需要插入很多记录，其中我还需要ID，以便在另一个表中插入更多记录。
因为当您向数据库提交更改时会分配 ID，所以我需要在特定时间调用 SubmitChanges。
当不需要时，我只需一次提交 1000 条左右（取决于我需要插入的记录总数）。
也许您可以做一些最适合您的速度测试。取决于硬件、记录量的期望等。
It also depends on the kind of data you need to insert. Sometimes I need to insert a lot of records, where I also need the ID of, to insert more records in another table.
Because the ID gets assigned when you submit changes to the database, I need to call SubmitChanges on specific times.
When this is not needed, I just submit them with a 1000 at once or so (depending on the total number of records I need to insert).
Maybe you could do some speed tests, which are best for you. Depending on the hardware, expectations of amount of records, etc.
好吧，我已经一次性完成了跨多个表的数十万条记录，没有出现任何问题。事实上，虽然在这种情况下为每个 InsertOnSubmit() 调用 SubmitChanges() 需要几个小时，但只需在最后调用 SubmitChanges() 即可将插入这么多记录所需的时间减少到几分钟。
在我上面描述的情况下，我的安排是用于报告带有标题表、详细信息表（链接到标题）和原子表（链接到详细信息）的表。对于每个标头记录，我都有多个详细表，然后这些表将再次由多个原子记录链接。在某些情况下，我最终会插入大量记录，并且最后的单个 SubmitChanges() 调用都不会出现任何问题，并且一切都执行得很好。
Well i've done it with hundreds of thousands of records across multiple tables in the one go without a problem. In fact, whereas calling SubmitChanges() for every InsertOnSubmit() in a case like that would hours, just calling SubmitChanges() right at the end would reduce the time takes to insert that many records to a couple of minutes.
In the case i described above, the arrangement i had was for reporting tables with a header table, details table (which linked to header) and an atom table (which linked to details). For every header record, i had multiple detail tables which would then again be linked by multiple atom records. In some cases, i'd end up inserting gazillions of records and they'd all go in no problems with the single SubmitChanges() call at the end and it all performed very well.
实际上没有任何“指南”。我会说，为了提高效率，你会想要收集一堆，也许不是 10k，而是说 100？这将大大减少您的数据库查询，并且在您建立事务时不会消耗太多在本地缓存它们的内存。
您可能应该使用几个不同的值进行测试并分析性能（内存和速度），以找到适合您的硬件的最佳解决方案。
There aren't really any "Guidelines" per-say. Id would say for efficiency you'd want to collect a bunch, maybe not 10k, but say 100? That would drastically reduce your DB Queries and shouldn't eat up too much ram caching them locally while you build up a transaction.
You should probably test with a couple different values and profile the performance (memory & speed) to find the optimal solution for your hardware though.