c# - 从 Web 服务处理数据库 - 如何提高性能？

Question

我创建了一个从客户端调用的 Web 服务，用于将数据存储到数据库中。这些数据每 200 毫秒从一个用户发送一次，每次发送数据时都会打开和关闭数据库连接，我认为这对性能不利。

通过以下方式调用 REST.StoreAcceleration() 方法和 SQLWorks.StoreAcceleration() 来存储数据:

public Response StoreAcceleration(string strSessionString, string strMeasurementTime, string strAccelerationX, string strAccelerationY, string strAccelerationZ)
    {
        SQLWorks sqlWorks = new SQLWorks();
        Response response = new Response();
        try
        {
            string strTime = strMeasurementTime.Replace("_", " ");
            DateTime measurementTime = DateTime.ParseExact(strTime, "yyyy-MM-dd HH:mm:ss:fff", null);
            double accelerationX = Convert.ToDouble(strAccelerationX.Replace(".", ","));
            double accelerationY = Convert.ToDouble(strAccelerationY.Replace(".", ","));
            double accelerationZ = Convert.ToDouble(strAccelerationZ.Replace(".", ","));

            sqlWorks.StoreAcceleration(strSessionString, measurementTime, accelerationX, accelerationY, accelerationZ);

            response.Successful = true;
            response.Comment = "Stored!";
        }
        catch(Exception ex)
        {
            string sDummy = ex.ToString();
            response.Comment = "an error occured!";
            response.Successful = false;
        }

        return response;
    }

public bool StoreAcceleration(string strStringSession, DateTime receivedTime, double accelerationX, double accelerationY, double accelerationZ)
    {
        bool result = false;
        string select =
            "INSERT INTO acceleration (session_id, measurement_time, acceleration_x, acceleration_y, acceleration_z) VALUES (@sessionID, @measurementTime, @accelerationX, @accelerationY, @accelerationZ)";
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(select, conn);
        int sessionID = getSessionID(strStringSession);
        if(sessionID == 0)
            return false;
        updateSessions(sessionID);
        string strRecordTime = receivedTime.ToString("yyyy-MM-dd HH:mm:ss:fff");
        cmd.Parameters.AddWithValue("sessionID", sessionID.ToString());
        cmd.Parameters.AddWithValue("measurementTime", strRecordTime);
        cmd.Parameters.AddWithValue("accelerationX", accelerationX.ToString());
        cmd.Parameters.AddWithValue("accelerationY", accelerationY.ToString());
        cmd.Parameters.AddWithValue("accelerationZ", accelerationZ.ToString());
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            result = true;
        }
        catch(Exception ex)
        {
            string sDummy = ex.ToString();
        }
        finally
        {
            conn.Close();
        }
        return result;
    }

这里的问题是 SqlConnection 在每次方法调用时都会打开和关闭。

如果有人可以建议如何改进解决方案以防止频繁打开/关闭数据库连接，我将不胜感激。

谢谢!

Answer 1

如果您设置了连接池，您的数据库连接将不会关闭。不要介意 conn.Close()。来自 MSDN:

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

如果您尚未使用它，请参阅此处进行设置: SQL Server connection pooling (ADO.NET)和 connection strings .

基本上，除非您的连接字符串中有 pooling=false 或类似内容，否则它应该已经处于事件状态。但是，您可能希望设置一些 MinPoolSize 以始终有几个连接可供使用。

---

顺便问一下，您是否将接收到的时间存储为实际字符串？否则你可以摆脱整个 ToString(..) 东西。 ADO.NET 将确保日期不会被误解。其他值也是如此；为什么要将它们转换为字符串？

最后，SqlCommand 实现了 IDisposable，因此您应该释放它，就像连接一样。我建议重写成这样:

public bool StoreAcceleration(string strStringSession, DateTime receivedTime, double accelerationX, double accelerationY, double accelerationZ)
{
    string select =
        "INSERT INTO acceleration (session_id, measurement_time, acceleration_x, acceleration_y, acceleration_z) VALUES (@sessionID, @measurementTime, @accelerationX, @accelerationY, @accelerationZ)";

    int sessionID = getSessionID(strStringSession);
    if (sessionID == 0)
        return false;
    updateSessions(sessionID);

    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(select, conn))
    {
        cmd.Parameters.AddWithValue("sessionID", sessionID);
        cmd.Parameters.AddWithValue("measurementTime", receivedTime);
        cmd.Parameters.AddWithValue("accelerationX", accelerationX);
        cmd.Parameters.AddWithValue("accelerationY", accelerationY);
        cmd.Parameters.AddWithValue("accelerationZ", accelerationZ);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

您不再需要在 finally block 中调用 Close()。当变量 conn 超出范围时，using block 将隐式调用 Dispose()。这将依次在内部调用 Close() 方法。