Where to store 1 billion records

Question

4.00/5 (1 vote)

See more:

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.

Posted 22-Feb-11 3:39am

Ahm12345

Add a Solution

4 solutions

Solution 2

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.

Posted 22-Feb-11 4:34am

Stuart_King

Solution 4

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[^]

Posted 22-Feb-11 6:11am

thatraja

Comments

Espen Harlinn 24-Feb-11 3:43am

Good links :)

Solution 3

I've dealt with data this size before. Why do you need all of it loaded to analyze and parse it? As mentioned earlier, do it in chunks. Also, bulk insert isn't that slow, do you have your indexes disabled while inserting? How many columns of what type of data are you talking about?

Posted 22-Feb-11 5:11am

wizardzz

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nish Nishant · Accepted Answer · 2011-02-22T03:41:00

Solution 1

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.

Posted 22-Feb-11 3:41am

Nish Nishant

Comments

Olivier Levrey 22-Feb-11 9:50am

Voted 5.

Nish Nishant 22-Feb-11 10:02am

Thanks.

Sergey Alexandrovich Kryukov 22-Feb-11 12:01pm

Yes, 5, what else?
--SA

Yusuf 22-Feb-11 12:40pm

VG +5

Shhh! He has the master list of either the Chinese or Indian population. I know some people are sneaking from the government and not included in the list. :-)

Espen Harlinn 24-Feb-11 3:42am

Good answer, my 5 - I'd try about 50000-100000 records at the time, using bulk insert