Click here to Skip to main content
15,844,673 members
Please Sign up or sign in to vote.
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

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.
 
Share this answer
 
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...

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900