大表查询时的连接超时 [英] 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.

该表有9,521,457行.

The table have 9,521,457 rows.

我要执行的查询是:

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

此查询在HeidiSQL上运行没有问题，耗时约171秒，返回434行.

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

但是当我运行C#脚本时，它在161行后超时.

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.

这是代码

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

推荐答案

command.CommandTimeout = int.MaxValue;

如果您更确切地知道要插入哪个号码，请执行此操作.如果将其设置为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.

这篇关于大表查询时的连接超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！