System OutOfMemory Exception

Question

1.00/5 (1 vote)

See more:

Hi I keep receiving OutOfMemory Excemption when retrieving 5millions of record on my Mysql
is their any possible way to retrieve Millions of Record on Mysql ?

VB

Using con As New MySqlConnection(conStr)
    con.Open()
    xTrans = con.BeginTransaction
    Using cmd As New MySqlCommand("LIMIT", con, xTrans)
        cmd.Parameters.AddWithValue("@LimitFromTo", LIMIT)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Prepare()
        Using conDap As New MySqlDataAdapter(cmd)
            conDap.Fill(ds)
        End Using
    End Using
    xTrans.Commit()
    con.Close()
End Using

Posted 27-May-13 20:57pm

iMaker.ph

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2013-05-27T21:04:00

Solution 2

Instead of using a DataSet and filling it, I would suggest that you consider a MySqlDataReader and process each record in turn. When you fill a dataset, all the records are fetched at the same time, which could use spectacular amounts of memory - A reader doesn't do that: it returns them one at a time as you demand them. Since trying to display 5,000,000 records at once would be a ridiculous thing to do as the user would be unable to find anything he wanted, it shouldn't inconvenience you too much.

Posted 27-May-13 21:04pm

OriginalGriff

Comments

iMaker.ph 28-May-13 3:11am

I Tried Datareader but the error still prompting :(

Using dRead As MySqlDataReader = cmd.ExecuteReader
While dRead.HasRows
dt.Load(dRead)
End While
dRead.Close()
End Using

Am I doing wrong with the Datareader ? o.O

OriginalGriff 28-May-13 3:36am

Yes - you are still trying to load all the records into a single datatable. Process the records individually, rather than trying to fetch them all at once.

DataAdapter.Fill uses a DataTable.Load(DataReader) in the background...

iMaker.ph 28-May-13 4:07am

What I understand with Datareader is that it read faster than DataAdapter.
But I keep receiving the error and OOM Exception pop up even retrieving 2M records.

OriginalGriff 28-May-13 5:19am

No - a datareader allows you to read a record at a time, instead of reading them all.

Atmir 28-May-13 9:31am

i had the same problem because , i had vs 32 bit and os 64 i resolved by installing both in 64 bit , but i never had 5 milion of records...

Dave Kreskowiak 28-May-13 11:15am

Let's do a little simple math here. How big is just ONE record?? Add the field widths together for all of the records. At 5,000,000 records, just retrieving a boolean field will take 5MB of memory! That's just ONE field! If you've got some 100 bytes per record (probably more!), that's going to add up to at least 500MB of RAM!! DO NOT USE A DATATABLE and DO NOT TRY TO LOAD THE ENTIRE TABLE INTO MEMORY!!

kumar2413 · Answer 2 · 2013-05-27T21:02:00

Solution 1

http://stackoverflow.com/questions/13648523/how-to-import-large-sql-file-using-mysql-exe-through-streamreader-standardinp[^]

Have a look in this website.

Posted 27-May-13 21:02pm

kumar2413