blocks|key|757785|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|757786|一次读取一条记录并插入到数据库中|blockquote|757787|757788|757789|批量读取，批量插入。|757790|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|J|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|K|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|L|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|M|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|N|7|@]|8|@]|9|$]]|$1|H|3|-4|4|5|6|O|7|@]|8|@]|9|$]]]|I|$]]

<blockquote>
 read the record one at the time and insert into the database
</blockquote>

Read them in batches and insert them in batches.

blocks|key|2677471|text|使用分析器-找出时间的去向。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2677472|如果没有真正的分析器，可以尝试以下方法：|2677473|2677474|Time仅逐行读取文件而不对其执行任何操作所需的时间|unordered-list-item|2677475|取一个样本行，并计算仅解析它并执行所需的任何处理所需的时间，5%2B百万次|2677476|生成随机数据并将其插入数据库的时间，以及|2677477|2677478|我的猜测是数据库将成为瓶颈。你应该考虑进行批量插入-如果你一次只插入一条记录，这可能比批量插入慢得多。|2677479|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|V|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

Use a profiler - find out where the time is going.

Short of a real profiler, try the following:

<ul>
<li>Time how long it takes to just read the files line by line, without doing anything with them</li>
<li>Take a sample line, and time how long it takes just to parse it and do whatever processing you need, 5+ million times</li>
<li>Generate random data and insert it into the database, and time that</li>
</ul>

My guess is that the database will be the bottleneck. You should look into doing a batch insert - if you're inserting just a single record at a time, that's likely to be a lot slower than batch inserting.

blocks|key|757763|text|我在过去做过很多这样的应用程序，你可以用很多方法来优化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|757764|757765|确保您正在编写的代码正确地管理内存，这里的一个小错误可能会使进程变得缓慢。|ordered-list-item|757766|考虑将数据库调用编写为异步的，因为它可能是瓶颈，所以稍微排队可能是可以的。|757767|考虑删除索引，执行导入，然后重新执行import.|757768|Consider使用SSIS执行导入，它已经进行了优化，并且开箱即用。|757769|757770|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|S|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|T|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

I have done many applications like this in the past and there are a number of ways that you can look at optimizing.

<ol>
<li>Ensure that the code you are writing is properly managing memory, with something like this one little mistake here can slow the process to a crawl.</li>
<li>Think about writing the database calls to be Async as it may be the bottleneck so a bit a queuing could be ok</li>
<li>Consider dropping indexes, doing the import then re-doing the import.</li>
<li>Consider using SSIS to do the import, it is already optimized and does this kind of thing out fo the box.</li>
</ol>

blocks|key|2677505|text|为什么不使用Microsoft+SQL+Server+Management+Studio或命令行SQLCMD将数据直接插入到SQL+Server数据库？它确实知道如何处理CVC文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2677506|在数据库上，BulkInsert属性应设置为True。|2677507|如果必须对其进行修改，可以将其插入到临时表中，然后使用T-SQL应用您的修改。|2677508|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Why not just insert that data directly to SQL Server Database using Microsoft SQL Server Management Studio or command line - SQLCMD? It does know how to process CVC files.

BulkInsert property should be set to True on your database.

If it has to be modified, you can insert it into Temprorary table and then apply your modifications with T-SQL.

blocks|key|4109807|text|最好的办法是尝试使用一个样本相对较小的分析器--这可以确定实际的阻塞点在哪里。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4109808|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Best bet would to try using a profiler with a relatively small sample -- this could identify where the actual hold-ups are.

blocks|key|329093|text|我会以同样的方式加速它:通过分析器运行它，并找出最长的时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329094|我们完全无法猜测瓶颈是什么--也许解析CSV文件的代码中存在一个bug，从而导致多项式运行时？也许有一些非常复杂的逻辑用来处理每一行？谁知道呢！|329095|而且，对于“记录”来说，500万行并不是那么重--一个胡思乱想的猜测说，一个合理的程序应该能够在半个小时内完成，一个好的程序应该用更少的时间。|329096|最后，如果您发现数据库是您的瓶颈，请检查在每次插入后是否正在提交事务。这可能会导致一些不平凡的减速。|329097|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

I'd speed it up the same way I'd speed anything up: by running it through a profiler and figuring out what's taking the longest.

There is absolutely no way to guess what the bottleneck here is -- maybe there is a bug in the code which parses the CSV file, resulting in polynomial runtimes? Maybe there is some very complex logic used to process each row? Who knows!

Also, for the "record", 5 million rows isn't all THAT heavy -- an off-the-top-of-my-head guess says that a reasonable program should be able to churn through that in half an hour, an a good program in much less.

Finally, if you find that the database is your bottleneck, check to see if a transaction is being committed after each insert. That can lead to some nontrivial slowdown...

blocks|key|329277|text|加载到内存中，然后插入到数据库中。500万行应该不会给你的内存带来负担。问题在于，您实际上是在颠簸您的磁盘--既读取CSV又写入DB。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329278|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Load it into memory and then insert into the DB. 5 million rows shouldn't tax your memory. The problem is you are essentially thrashing your disk--both reading the CSV and writing to the DB.

blocks|key|329295|text|不知道您在用它们做什么，但是您考虑过perl吗？我最近重写了一个vb脚本，它正在做类似的事情-处理数千条记录-时间从vb脚本的大约一个小时到perl的大约15秒。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329296|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Not sure what you're doing with them, but have you considered perl? I recently re-wrote a vb script that was doing something similar - processing thousands of records - and the time went from about an hour for the vb script to about 15 seconds for perl.

blocks|key|757841|text|从文件中读取所有记录后(我会一次性读取整个文件，或分块读取)，然后使用SqlBulkCopy类将记录导入到数据库中。据我所知，SqlBulkCopy是导入记录块的快速方法。有许多在线教程。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|757842|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

After reading all records from file (I would read entire file in one pass, or in blocks), then use the SqlBulkCopy class to import your records into the DB. SqlBulkCopy is, as far as I know, the fasted approach to importing a block of records. There are a number of tutorials online.

blocks|key|4109999|text|正如其他人所建议的那样，首先对应用程序进行分析。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4110000|也就是说，您可能会从批量插入中获益。这就是我使用的一个应用程序的案例，它的影响很大。|4110001|考虑到500万次往返是很多的，特别是如果每个往返都是为了一个简单的插入。|4110002|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

As others has suggested, profile the app first.

That said, you will probably gain from doing batch inserts. This was the case for one app I worked with, and it was a high impact. 

Consider 5 million round trips are a lot, specially if each of them is for a simple insert.

blocks|key|757882|text|在类似的情况下，通过从单行插入切换到使用SqlBulkCopy应用编程接口，我们看到了相当大的性能改进。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|757883|有一篇很好的文章here。|757884|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx|1|http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server^0|K|B|0|0|8|4|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

In a similar situation we saw considerable performance improvement by switching from one-row-at-time inserts to using the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx" rel="nofollow noreferrer">SqlBulkCopy</a> API.

There is a good article <a href="http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server" rel="nofollow noreferrer">here</a>.

blocks|key|2670380|text|假设数据库具有该功能，则需要将数据批量加载到数据库中。在Sql+Server中，您可能会看到BCP、DTS或SSIS+-+BCP是最古老的，但可能是最快的。如果这在你的数据库中是不可能的，那么在运行之前关闭所有的索引，我猜是数据库导致了问题，而不是.Net代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2670381|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You need to bulk load the data into your database, assuming it has that facility. In Sql Server you'd be looking at BCP, DTS or SSIS - BCP is the oldest but maybe the fastest. OTOH if that's not possible in your DB turn off all indexes before doing the run, I'm guessing it's the DB that's causing problems, not the .Net code.

I wrote a VB.net console program to process CSV record that come in a text file. I'm using <a href="http://filehelpers.sourceforge.net/" rel="nofollow noreferrer">FileHelpers library</a>

along with MSFT Enterprise library 4. To read the record one at the time and insert into the database. 

It took about 3 - 4 hours to process 5+ million records on the text file.

Is there anyway to speed up the process? Has anyone deal with such large amount of records before and how would you update such records if there is new data to be update?

edit: Can someone recommend a profiler? prefer Open source or free.

How do you speed up CSV file process? (5 million or more records)

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我编写了一个VB.net控制台程序来处理文本文件中的CSV记录。我正在使用以及MSFT企业程序库4.一次读取一条记录并插入到数据库中。处理文本文件上的5+百万条记录大约需要3-4个小时。有没有什么办法可以加速这个过程？以前是否有人处理过如此大量的记录?如果有新数据需要更新，您将如何更新这些记录？编辑:有人能推荐一个分析器吗？更喜欢开源或免费。

问如何加快CSV文件处理速度？(500万条或更多记录)
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何加快CSV文件处理速度？(500万条或更多记录)EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何加快CSV文件处理速度？(500万条或更多记录)
EN