哪里存储10亿条记录 [英] Where to store 1 billion records

问题描述

你好！我需要从txt文件中读取大约10亿行，并将其放入SQL Server数据库后进行处理.

1)当我创建解析器并尝试将它们存储在集合(List或其他)中时，出现内存不足的异常.我应该使用哪种类型的集合来成功读取和存储此数量的数据?
2)将数据直接插入数据库会更好吗?我尝试了bulkinsert，但它似乎变慢了.

谢谢.

Hello! I need to read from txt file about 1 billion rows and after manipulation put them in sql server db.

1) When I create a parser and try to store them in collection (List<> or another) I got outofmemoryexception. What kind of collection I should use to read and store successfully this amount of data?
2) How it''ll be better to insert data directly to DB? I tried bulkinsert, but it seems to slow.

Thanks.

推荐答案

首先，我有点怀疑一个文本文件(或任何平面文件)将拥有十亿条记录.但是假设这只是一个模糊的词，曾经是一个非常大的文件，那么您不应该尝试读取整个数据，然后将其写入数据库.逐块读取(例如一次读取3000条记录)，然后继续写入数据库.

First of all I am a little skeptical that a text file (or any flat file) will have one billion records. But assuming that that''s just a vague word used to mean a really large file, then you should not try and read the entire data and then write it to the database. Read it in chunks (say 3000 records at a time) and then keep writing to the database.

如果有可用的记录，我会考虑使用SSIS


它将允许您操纵数据并将其存储在数据库中.另外，如果这是可重复的任务，则也可以安排它.设置SSIS软件包的速度可能会很快，因为它将处理分块/提交/回滚等问题.

If you have it available, I would look into using SSIS


It will allow you to manipulate your data and store in the DB. Also if it is a repeatable task, you can schedule it too. It could be just as quick to set up an SSIS package as it will take care of chunking/commits/rollbacks etc.

在这里，您真是花花公子....看看这些线程

在SQL Server(C#客户端)中批量插入大量数据的最快方法是什么? [替代SQL BULK INSERT [最佳批量插入命令 [ ^ ]

Here you go dude....look at these threads

What''s the fastest way to bulk insert a lot of data in SQL Server (C# client)[^]

Alternative to SQL BULK INSERT[^]

Best bulk insert command[^]

这篇关于哪里存储10亿条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！