Connection timeout on query on large table

问题:

I have a problem with a script timing out while fetching data form a query on large table.

The table have 9,521,457 rows.

The query I'm trying to preform is:

SELECT * 
FROM `dialhistory` 
WHERE `customerId` IN (22606536, 22707251, 41598836);

This query runs without problems on HeidiSQL and take about 171 seconds and returns 434 rows.

But when I run my C# script dose it timeout after 161 rows.

16:54:55: Row 1
...
16:54:55: Row 161
16:55:32: Error -> Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the code

public MySqlDatabase(string server, string database, string username, string password)
{
    ConnectionString = "SERVER=" + server + ";DATABASE=" + database + ";UID=" + username + ";PASSWORD=" + password + ";";

}

public IQueryable<DailHistory> GetHistory(IList<int> customerIds)
{
    IList<DailHistory> list = new List<DailHistory>();
    var connection = new MySqlConnection(ConnectionString);
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = "SELECT * FROM `dialhistory` WHERE `customerId` in ("+string.Join(",", customerIds.ToArray())+")";
    var reader = command.ExecuteReader();
    int i = 1;
    while (reader.Read())
    {
        Console.WriteLine(DateTime.Now.ToLongTimeString() + ": Row " + i);
        i++;
        try
        {
            var d = new DailHistory();
            d.CustomerId = int.Parse((string) reader["customerId"]);
            d.Agent = ParseNullAbleString(reader["agent"].ToString());
            d.CallBackReason = ParseNullAbleString(reader["callBackReason"].ToString());
            d.CallState = ParseCallSate(reader["callState"].ToString());
            d.ContactResponse = ParseNullAbleString(reader["contactResponse"].ToString());
            d.DailTime = new DailTime(reader["dialStart"].ToString(), reader["dialEnd"].ToString());
            d.HistoryIndex = int.Parse(reader["historyIndex"].ToString());
            d.Note = ParseNullAbleString(reader["note"].ToString());
            d.OldDialNo = ParseNullAbleInt(reader["oldDialNo"].ToString());
            d.ProjectJob = ParseNullAbleString(reader["projectJob"].ToString());
            list.Add(d);
        }
        catch(Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }
    reader.Close();
    return list.AsQueryable();
}

回答1:

command.CommandTimeout = int.MaxValue;

If you know more exactly which number to insert, do that. If you set it to int.MaxValue, you are removing a security barrier.

回答2:

Set the CommandTimeout on the command object

var command = connection.CreateCommand();
command.CommandTimeout = 0;
//zero specifies never timeout. 
//Any number greater than zero is the number of seconds before 
//the command will time out.

回答3:

Add an index on the customerId column.

回答4:

command.CommandTimeout = 2147483;

The largest value for a MySQL command timeout is the largest value for a 32 bit integer, in milliseconds, 2147483647. But in C# the CommandTimeout property is in seconds, not milliseconds, so any higher than 2147483 will result in an exception.

Although this is not infinite, it is 24 days, 20 hours, 31 minutes, and 23 seconds, which would hopefully meet your need.

Setting the value to 0 did not work for me. The CommandTimeout property would not retain the value of 0 and kept auto-changing back to 30.

Setting the value to -1 did seem to work, but I didn't test it enough to be certain that a timeout would never occur.

Safest option: go with 2147483.