Connection timeout on query on large table

Question

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();
}

Answer 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.

Answer 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.