C#循环执行SQL查询匹配日志与SQL Server数据遇异常及性能问题求助

当前代码的核心问题 #

1. 日期格式错误：手动拼接的日期为年-日-月，不符合SQL Server的datetime解析规则，导致绝大多数查询返回空结果，这是匹配失败的主要原因。
2. 性能极差：循环内逐次执行SQL查询，若registros数量大，会产生成百上千次数据库请求，网络开销和数据库压力都不可接受。
3. 空catch吞异常：无法捕获调试过程中的错误，比如数据类型转换失败、字段不存在等问题。
4. 查询逻辑冗余：先按日期查询所有记录，再在内存中筛选零件号，没有利用数据库的查询优化能力。

临时修复（解决匹配问题，不优化性能） #

先修正日期格式并改用参数化查询，避免手动拼接的错误和SQL注入风险：

for (int i = 0; i < registros.Count; i++) {
    // 参数化查询，直接传入DateTime对象，无需手动转换
    command = new SqlCommand(@"
        Select DateTime, P15, Reference 
        from ProductionDay 
        where DateTime = @Date and Reference = @PartNumber
    ", cnn);
    command.Parameters.AddWithValue("@Date", registros[i].day);
    command.Parameters.AddWithValue("@PartNumber", registros[i].part_number.Trim());

    Console.WriteLine($"{i:D5} - Checking {registros[i].part_number}");
    
    using (SqlDataReader reader = command.ExecuteReader()) {
        while (reader.Read()) {
            Console.WriteLine("SQL Matched!");
            try {
                registros[i].sql_part_number = registros[i].part_number;
                // 直接读取int类型，避免字符串转换开销
                registros[i].quantity += (int)reader["P15"];
            } catch (Exception ex) {
                // 输出异常信息，便于调试
                Console.WriteLine($"Error at index {i}: {ex.Message}");
            }
        }
    }
}

最优解决方案：批量处理（彻底解决性能问题） #

核心思路是将多次数据库请求合并为一次，利用SQL Server的批量处理能力提升效率，以下两种方案按需选择：

方案1：表值参数批量查询（适用于日志数量中等场景）

1. 先在SQL Server创建自定义表值类型：

CREATE TYPE LogItemType AS TABLE (
    LogDate DATETIME,
    PartNumber NVARCHAR(100)
)

2. C#代码实现：

// 1. 准备批量查询的数据集
DataTable logTable = new DataTable();
logTable.Columns.Add("LogDate", typeof(DateTime));
logTable.Columns.Add("PartNumber", typeof(string));

foreach (var item in registros) {
    logTable.Rows.Add(item.day, item.part_number.Trim());
}

// 2. 执行一次批量关联查询
using (SqlCommand command = new SqlCommand(@"
    SELECT p.P15, l.PartNumber
    FROM ProductionDay p
    INNER JOIN @LogItems l 
        ON p.DateTime = l.LogDate AND p.Reference = l.PartNumber
", cnn)) {
    // 传入表值参数
    SqlParameter tvpParam = command.Parameters.AddWithValue("@LogItems", logTable);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.LogItemType";

    using (SqlDataReader reader = command.ExecuteReader()) {
        // 3. 把匹配结果存入字典，快速匹配更新
        Dictionary<string, int> matchDict = new Dictionary<string, int>();
        while (reader.Read()) {
            string partNumber = reader["PartNumber"].ToString();
            int p15 = (int)reader["P15"];
            matchDict[partNumber] = matchDict.TryGetValue(partNumber, out int val) ? val + p15 : p15;
        }

        // 4. 批量更新本地集合
        for (int i = 0; i < registros.Count; i++) {
            string key = registros[i].part_number.Trim();
            if (matchDict.TryGetValue(key, out int quantity)) {
                registros[i].sql_part_number = registros[i].part_number;
                registros[i].quantity += quantity;
                Console.WriteLine($"{i:D5} - Matched {key}, added {quantity} to quantity");
            }
        }
    }
}

方案2：临时表+BulkCopy（适用于日志数量极大场景）

如果日志数据量也很大，将数据导入SQL临时表后做关联查询，效率更高：

// 1. 创建临时表并导入日志数据
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn)) {
    // 先创建临时表，保留原集合索引用于后续更新
    using (SqlCommand createTempCmd = new SqlCommand(@"
        CREATE TABLE #TempLogItems (
            OriginalIndex INT,
            LogDate DATETIME,
            PartNumber NVARCHAR(100)
        )
    ", cnn)) {
        createTempCmd.ExecuteNonQuery();
    }

    bulkCopy.DestinationTableName = "#TempLogItems";
    DataTable logTable = new DataTable();
    logTable.Columns.Add("OriginalIndex", typeof(int));
    logTable.Columns.Add("LogDate", typeof(DateTime));
    logTable.Columns.Add("PartNumber", typeof(string));

    for (int i = 0; i < registros.Count; i++) {
        logTable.Rows.Add(i, registros[i].day, registros[i].part_number.Trim());
    }

    bulkCopy.WriteToServer(logTable);
}

// 2. 执行关联查询获取匹配结果
using (SqlCommand queryCmd = new SqlCommand(@"
    SELECT t.OriginalIndex, SUM(p.P15) AS TotalQuantity
    FROM #TempLogItems t
    INNER JOIN ProductionDay p 
        ON t.LogDate = p.DateTime AND t.PartNumber = p.Reference
    GROUP BY t.OriginalIndex
", cnn)) {
    using (SqlDataReader reader = queryCmd.ExecuteReader()) {
        while (reader.Read()) {
            int idx = (int)reader["OriginalIndex"];
            int total = (int)reader["TotalQuantity"];
            registros[idx].sql_part_number = registros[idx].part_number;
            registros[idx].quantity += total;
            Console.WriteLine($"{idx:D5} - Matched, added {total} to quantity");
        }
    }
}

// 3. 删除临时表
using (SqlCommand dropTempCmd = new SqlCommand("DROP TABLE #TempLogItems", cnn)) {
    dropTempCmd.ExecuteNonQuery();
}

额外优化建议 #

• 给ProductionDay表加联合索引：大幅提升关联查询速度：

CREATE NONCLUSTERED INDEX IX_ProductionDay_DateTime_Reference 
ON ProductionDay (DateTime, Reference) 
INCLUDE (P15);

• 禁用空catch块：异常信息是调试关键，至少要输出错误内容。
• 永远使用参数化查询：避免SQL注入和格式解析错误。

内容的提问来源于stack exchange，提问作者Raphael Frei