现有可运行代码需重构提速：文本框返回结果耗时过长问题

问题描述 #

我有一段可运行的代码，但将结果返回至文本框耗时极长，最长可达20秒，且待处理的数据量并不大。代码能输出正确结果，但性能表现不佳。

原始代码：

string newLine = Environment.NewLine;

public List<string> AppointmentTypesForReport(int month) {
    List<string> appointments = new List<string>();
    string CS = ConfigurationManager.ConnectionStrings["U04i5a"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(CS)) {
        try {
            con.Open();
            MySqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT type FROM appointment WHERE MONTH(start) = @month";
            cmd.Parameters.AddWithValue("@month", month);
            cmd.ExecuteNonQuery();
            using (MySqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    appointments.Add(reader["type"].ToString());
                }
            }
        } catch (Exception) {
            throw;
        }
    }
    return appointments;
}

private void TypesByMonthRadioButton_CheckedChanged(object sender, EventArgs e) {
    ReportsTextBox.Clear();
    var typesOutput = "";
    for (int m = 1; m <= 6; m++) {
        List<string> list = AppointmentTypesForReport(m);
        var j = from t in list group t by t into g let totalNumber = g.Count() orderby totalNumber descending select new { Returned = g.Key, Tally = totalNumber };
        typesOutput += newLine + DateTimeFormatInfo.CurrentInfo.GetMonthName(m) + newLine;
        foreach (var t in j) {
            typesOutput += "Type of appointment scheduled: " + t.Returned + " " + "-Appointments of this type: " + t.Tally + newLine;
        }
    }
    ReportsTextBox.Text = typesOutput;
}

代码运行时文本框输出：

January
Type of appointment scheduled: xyz -Appointments of this type: 2
February
March
April
May
Type of appointment scheduled: General Doctor -Appointments of this type: 1
June
Type of appointment scheduled: General Doctor -Appointments of this type: 1

核心性能瓶颈分析 #

咱们先拆解一下慢的原因：

• 多次数据库往返：循环调用AppointmentTypesForReport6次，每次都要建立连接、执行查询，光是数据库的往返时间就占了大头，哪怕数据量小，多次交互的累积开销也很大
• 低效字符串拼接：用string类型做多次+=操作，每次都会生成新的字符串对象，频繁的内存分配和回收会拖慢速度
• 冗余代码：cmd.ExecuteNonQuery();完全是多余的，它会额外执行一次查询却不读取结果，白白浪费数据库资源

优化方案 #

1. 合并数据库查询，减少交互次数

把6次查询合并成1次，一次性获取1-6月的所有数据，然后在内存中分组统计，这样只需要1次数据库往返：

// 新增一个类来存储月份和类型的关联数据
private class AppointmentTypeMonth
{
    public int Month { get; set; }
    public string Type { get; set; }
}

public List<AppointmentTypeMonth> GetAllAppointmentTypesForMonths()
{
    List<AppointmentTypeMonth> appointments = new List<AppointmentTypeMonth>();
    string CS = ConfigurationManager.ConnectionStrings["U04i5a"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(CS))
    {
        try
        {
            con.Open();
            MySqlCommand cmd = con.CreateCommand();
            // 一次性查询1-6月的预约数据，同时返回月份和类型
            cmd.CommandText = "SELECT MONTH(start) AS Month, type FROM appointment WHERE MONTH(start) BETWEEN 1 AND 6";
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    appointments.Add(new AppointmentTypeMonth
                    {
                        Month = Convert.ToInt32(reader["Month"]),
                        Type = reader["type"].ToString()
                    });
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }
    return appointments;
}

2. 使用StringBuilder优化字符串拼接

替换原来的string拼接为StringBuilder，它专门针对多次拼接场景做了优化，避免频繁的内存分配：

private void TypesByMonthRadioButton_CheckedChanged(object sender, EventArgs e)
{
    ReportsTextBox.Clear();
    StringBuilder typesOutput = new StringBuilder();
    var allAppointments = GetAllAppointmentTypesForMonths();

    // 先按月份分组，方便后续处理每个月的数据
    var monthlyGroups = allAppointments.GroupBy(a => a.Month);

    for (int m = 1; m <= 6; m++)
    {
        // 添加月份名称
        typesOutput.AppendLine(DateTimeFormatInfo.CurrentInfo.GetMonthName(m));
        
        // 获取当前月份的所有预约类型数据
        var monthData = monthlyGroups.FirstOrDefault(g => g.Key == m);
        if (monthData != null)
        {
            // 按类型分组统计数量，并按数量降序排列
            var typeStats = monthData.GroupBy(t => t.Type)
                                     .Select(g => new { Returned = g.Key, Tally = g.Count() })
                                     .OrderByDescending(x => x.Tally);
            
            foreach (var t in typeStats)
            {
                typesOutput.AppendLine($"Type of appointment scheduled: {t.Returned} - Appointments of this type: {t.Tally}");
            }
        }
        
        // 添加空行分隔月份，和原输出格式保持一致
        typesOutput.AppendLine();
    }

    // 移除最后多余的空行（可选，根据需求调整）
    if (typesOutput.Length > 0)
    {
        typesOutput.Length -= Environment.NewLine.Length;
    }

    ReportsTextBox.Text = typesOutput.ToString();
}

3. 移除冗余代码

把原始AppointmentTypesForReport方法里的cmd.ExecuteNonQuery();删掉——ExecuteReader已经会执行查询并返回结果，这行代码完全是多余的。

额外优化建议 #

• 利用数据库索引：如果start字段有索引，MONTH(start) BETWEEN 1 AND 6可能无法触发索引，可以改成按日期范围查询，比如start >= '2024-01-01' AND start < '2024-07-01'（替换成实际年份），这样能利用索引加速查询
• 异步查询避免UI阻塞：改成异步方法，用await执行数据库操作，防止UI线程被冻结，提升用户体验：

public async Task<List<AppointmentTypeMonth>> GetAllAppointmentTypesForMonthsAsync()
{
    List<AppointmentTypeMonth> appointments = new List<AppointmentTypeMonth>();
    string CS = ConfigurationManager.ConnectionStrings["U04i5a"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(CS))
    {
        await con.OpenAsync();
        MySqlCommand cmd = con.CreateCommand();
        cmd.CommandText = "SELECT MONTH(start) AS Month, type FROM appointment WHERE MONTH(start) BETWEEN 1 AND 6";
        using (MySqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                appointments.Add(new AppointmentTypeMonth
                {
                    Month = Convert.ToInt32(reader["Month"]),
                    Type = reader["type"].ToString()
                });
            }
        }
    }
    return appointments;
}

// 事件处理方法改成async
private async void TypesByMonthRadioButton_CheckedChanged(object sender, EventArgs e)
{
    ReportsTextBox.Clear();
    StringBuilder typesOutput = new StringBuilder();
    var allAppointments = await GetAllAppointmentTypesForMonthsAsync();

    // 后续逻辑和之前的同步版本一致...
}

这样优化后，数据库交互次数从6次降到1次，字符串拼接效率也大幅提升，应该能把耗时从20秒压缩到几百毫秒以内。

内容的提问来源于stack exchange，提问作者Joshua Johnson