C#中SQLServer与Access批量数据插入性能对比

1. Access 数据库代码实现

以下是将多条紧固结果数据保存到Access数据库的方法：

private void SaveResultToMyaccess(PMOpenProtocol.TighteningResultData data) {
    try {
        myAccess.AccessDbClass();
        OleDbTransaction myTrans = myAccess.Conn.BeginTransaction();
        OleDbCommand cmd = myAccess.Conn.CreateCommand();
        cmd.Transaction = myTrans;
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.Append("INSERT INTO mydata (TighteningID, ProductSN, PsetName, StationName, BoltNumber, TighteningStatus, ResultDateTime, FinalTorque, FinalAngle, TorqueMax, TorqueMin, OperateDateTime, OperateFlag, ErrorInfo)");
        sqlBuilder.Append(" VALUES (@TighteningID, @ProductSN, @PsetName, @StationName, @BoltNumber, @TighteningStatus, @ResultDateTime, @FinalTorque, @FinalAngle, @TorqueMax, @TorqueMin, @OperateDateTime, @OperateFlag, @ErrorInfo)");
        for (int i = 1; i <= data.Number_of_Bolts; i++) {
            using (OleDbCommand incmd = new OleDbCommand(sqlBuilder.ToString(), myAccess.Conn)) {
                incmd.Parameters.AddWithValue("@TighteningID", data.TighteningID);
                incmd.Parameters.AddWithValue("@ProductSN", data.IDRes);
                incmd.Parameters.AddWithValue("@PsetName", data.ModeName);
                incmd.Parameters.AddWithValue("@StationName", data.StationName);
                incmd.Parameters.AddWithValue("@BoltNumber", type.GetField("OrdinalBoltNumber_" + i).GetValue(data));
                incmd.Parameters.AddWithValue("@TighteningStatus", type.GetField("TighteningStatus_" + i).GetValue(data));
                incmd.Parameters.AddWithValue("@ResultDateTime", Convert.ToDateTime(data.t_D_REAL_TIME));
                incmd.Parameters.AddWithValue("@FinalTorque", Convert.ToDouble(type.GetField("FinalTorque_" + i).GetValue(data)));
                incmd.Parameters.AddWithValue("@FinalAngle", Convert.ToDouble(type.GetField("FinalAngle_" + i).GetValue(data)));
                incmd.Parameters.AddWithValue("@TorqueMax", Convert.ToDouble(type.GetField("TorqueMax_" + i).GetValue(data)));
                incmd.Parameters.AddWithValue("@TorqueMin", Convert.ToDouble(type.GetField("TorqueMin_" + i).GetValue(data)));
                incmd.Parameters.AddWithValue("@OperateDateTime", "");
                incmd.Parameters.AddWithValue("@OperateFlag", 0);
                incmd.Parameters.AddWithValue("@ErrorInfo", "");
                incmd.ExecuteNonQuery();
            }
        }
        myTrans.Commit();
        myAccess.Close();
    } catch (Exception e) {
        log.Warn("写入数据库失败，请检查数据库：" + e.ToString());
        myAccess.Close();
    }
}

2. SQL Server 数据库代码实现

以下是将多条紧固结果数据保存到SQL Server数据库的方法：

public void SaveMultiTighteningResult(PMOpenProtocol.TighteningResultData data) {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.Append("INSERT INTO TighteningResult (TighteningID, ProductSN, StationCode, StationName, PsetName, BoltNumber, TighteningStatus, ResultDateTime, FinalTorque, FinalAngle, TorqueMax, TorqueMin, OperateDateTime, OperateFlag, ErrorInfo)");
    sqlBuilder.Append(" SELECT @TighteningID, @ProductSN, '', @StationName, @PsetName, @BoltNumber, @TighteningStatus, @ResultDateTime, @FinalTorque, @FinalAngle, @TorqueMax, @TorqueMin, '', @OperateFlag, ''");
    Type type = data.GetType();
    string sql = "";
    for (int i = 1; i <= data.Number_of_Bolts; i++) {
        if (i == 1) {
            sql = string.Format(sqlBuilder.ToString(),
                data.TighteningID, data.IDRes, "", data.StationName, data.ModeName,
                type.GetField("OrdinalBoltNumber_1").GetValue(data), type.GetField("TighteningStatus_1").GetValue(data),
                Convert.ToDateTime(data.t_D_REAL_TIME), Convert.ToDouble(type.GetField("FinalTorque_1").GetValue(data)),
                Convert.ToDouble(type.GetField("FinalAngle_1").GetValue(data)), Convert.ToDouble(type.GetField("TorqueMax_1").GetValue(data)),
                Convert.ToDouble(type.GetField("TorqueMin_1").GetValue(data)), "", 0, "");
        } else {
            sqlBuilder.Append(" UNION ALL SELECT @TighteningID, @ProductSN, '', @StationName, @PsetName, @BoltNumber, @TighteningStatus, @ResultDateTime, @FinalTorque, @FinalAngle, @TorqueMax, @TorqueMin, '', @OperateFlag, ''");
            sql = string.Format(sqlBuilder.ToString(),
                data.TighteningID, data.IDRes, "", data.StationName, data.ModeName,
                type.GetField("OrdinalBoltNumber_" + i).GetValue(data), type.GetField("TighteningStatus_" + i).GetValue(data),
                Convert.ToDateTime(data.t_D_REAL_TIME), Convert.ToDouble(type.GetField("FinalTorque_" + i).GetValue(data)),
                Convert.ToDouble(type.GetField("FinalAngle_" + i).GetValue(data)), Convert.ToDouble(type.GetField("TorqueMax_" + i).GetValue(data)),
                Convert.ToDouble(type.GetField("TorqueMin_" + i).GetValue(data)), "", 0, "");
        }
    }
    try {
        SQLHelper.Update(sql);
    } catch (SqlException ex) {
        log.Warn("数据库操作出现异常！具体信息：" + ex.Message);
    } catch (Exception ex) {
        log.Warn("异常！具体信息：" + ex.Message);
    }
}

3. 性能对比测试

为了评估SQL Server和Access数据库的性能，我们进行了以下测试：

Stopwatch sw = new Stopwatch();
sw.Start();
SaveMultiTighteningResult(data); // 将数据保存到SQL Server数据库
sw.Stop();
Console.WriteLine("SQL Server用时：" + sw.Elapsed.TotalMilliseconds + " 毫秒");
Stopwatch sw2 = new Stopwatch();
sw2.Start();
SaveResultToMyaccess(data); // 将数据保存到Access数据库
sw2.Stop();
Console.WriteLine("Access用时：" + sw2.Elapsed.TotalMilliseconds + " 毫秒");

测试结果显示：
SQL Server用时：172.4197 毫秒
Access用时：454.1316 毫秒

为了不影响界面的卡顿，建议将数据保存操作放入线程中处理：

Stopwatch sw = new Stopwatch();
sw.Start();
Thread th2 = new Thread(() => SaveMultiTighteningResult(data)); // 将数据保存到SQL Server数据库
th2.Start();
sw.Stop();
Console.WriteLine("SQL Server用时：" + sw.Elapsed.TotalMilliseconds + " 毫秒");
Stopwatch sw2 = new Stopwatch();
sw2.Start();
Thread th = new Thread(() => SaveResultToMyaccess(data)); // 将数据保存到Access数据库
th.Start();
sw2.Stop();
Console.WriteLine("Access用时：" + sw2.Elapsed.TotalMilliseconds + " 毫秒");