1

I have a big table which contains about 100 million records in MySQL. I want to read all the records from it and process them in my C#/.Net program, so I wrote some code like this:

string sql = "SELECT * FROM the_table";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//...
}

But after running for 3 minutes, a timeout problem occurs. This is the error output:

If I change the SQL statement to use "limit":

string sql = "SELECT * FROM the_table limit 60000000" //60 million

then it works OK. (the loop while (rdr.Read()) took about 3.8 minutes)

Was the timeout caused because the table was too big?

What's going on here and how do I fix the problem?

ridecar2
  • 1,968
  • 16
  • 34
Hancy
  • 513
  • 2
  • 5
  • 9
  • 2
    Why are you trying to slurp 100million records into memory? That defeats the purpose of having a database and reduces the entire sql infrastructure into a dumb storage system. – Marc B Dec 30 '11 at 12:48
  • As @MarcB is probably more correct, anyway what happens if you select [onefield] from the_table instead of *?, maybe the sql infrastructure is working perfectly but your client is simply out of memory... – rene Dec 30 '11 at 12:59
  • Well, actually I meet the 'timeout' problem when I joint two table and select part data of it(one of the table is the big table mentioned in the question). Then I found the 'timeout' occur even I wrote "SELECT * FROM the_table". I think if I can fix one, I can fix another. so I choose a simple way to ask. @MarcB – Hancy Dec 30 '11 at 13:06
  • I'm sure it's not out of memory. MySqlDataReader avoids creating unnecessary objects or making unnecessary copies of data. @rene – Hancy Dec 30 '11 at 13:11
  • Does it also break if you run it at the server? – rene Dec 30 '11 at 13:18
  • Yes. I'm doing debug at the server. @rene – Hancy Dec 30 '11 at 13:22
  • Does the query produce all results if you run in from the mysql commandline tool? – rene Dec 30 '11 at 13:24
  • 100 000 000 rows at 10 bytes per row is near 1 gigabyte (2 int columns and 1 smallint column). And that's native implementation of storing the data. So you're sure that something that creates objects and data structures to store data sent over the wire will not use **slightly** more memory? I just don't know why would anyone, even for testing purposes, read out 100 mil records at once. – N.B. Dec 30 '11 at 13:30
  • do you use tcp/ip for the connection? Can you check what happens if you use shared memory? Also check the mysql logs. I have had trouble once were I needed to fiddle with some obscure mysql settings (timeouts/buffersizes) on the server to get a particular import going. – rene Dec 30 '11 at 13:35
  • @rene Yes, I use tcp/ip for the connection. I think it's not the mysql setting problem, because I tried the statement `select * into outfile 'test.txt' from the_table` on the command line tool, the query works fine. It look like the problem was cause by class MySqlCommand.ExecuteReader() or MySqlDataReader.Read(). – Hancy Dec 31 '11 at 00:57
  • I've localize the problem, thank you for your help.@rene – Hancy Dec 31 '11 at 03:52

2 Answers2

1

OK, I finally localized the problem! It was because I used ""ADO.NET Driver for MySQL (Connector/Net) which was developed by MySQL. And there may be some bugs. Then I change to use dotConnect , the problem was gone. Thank for all help.

Hancy
  • 513
  • 2
  • 5
  • 9
  • I'm having exactly the same problem. Is this a bug in the MySql.NET connector? You shouldn't have to purchase more software to get this working. – mcmillab Jun 16 '14 at 09:18
0

I have set command timeout to a higher value and it fixed the issue.

 MySqlCommand mySqlCommand = new MySqlCommand(sql_cmd);
 mySqlCommand.Connection = _connection;
 mySqlCommand.CommandTimeout = 3600;

Hope this helps

anuradha
  • 123
  • 1
  • 9