c# - 单独的 SQL 命令与单个存储过程的性能

Question

我继承了一些与 GP 交互的代码。在调试问题之前查看代码时，我发现了一个函数可以撤消对一组表的更新。

该代码包含许多 SQL 命令，我想知道与在每一行上运行所有这些命令相比，使用输入参数为表 ds 的单个存储过程是否会获得更好的性能表的单独。

    private void revertSave()
    {
        try
        {
            SqlConnection conn = GetConnectionToGP();
            if (conn != null)
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //records from InventoryTransaction Master, Detail, and Serial tables when window is opened.
                    foreach (DataRow dr in _ds.Tables[0].Rows)
                    {
                        cmd.Connection = conn;
                        //TRANSACTION tables
                        cmd.CommandText = "UPDATE InventoryTransaction_Master " +
                            "SET Completed = 0 WHERE DocumentNumber = " + SqlString(dr["DocumentNumber"].ToString().Trim());
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "UPDATE InventoryTransaction_Serial " +
                            "SET BIN = '' " +
                            "WHERE DocumentNumber = " + SqlString(dr["DocumentNumber"].ToString().Trim()) +
                            " and DocumentType = 1 and ItemNumber = " + SqlString(dr["PrefixedItemNumber"].ToString().Trim()) +
                            " and LineSequenceNumber = " + SqlString(dr["LineSequenceNumber"].ToString().Trim()) +
                            " and SerialNumber = " + SqlString(dr["SerialNumber"].ToString().Trim());
                        cmd.ExecuteNonQuery();

                        //TRANSFER tables
                        cmd.CommandText = "SELECT DISTINCT detail.DocumentNumber " +
                            "FROM  InventoryTransfer_Detail detail " +
                            "INNER JOIN InventoryTransfer_Serial serial ON detail.DocumentNumber = serial.DocumentNumber " +
                            "and detail.ItemNumber = serial.ItemNumber " +
                            "and detail.LineSequenceNumber = serial.LineSequenceNumber " +
                            "WHERE SessionID = " + SqlString(dr["SessionID"].ToString().Trim()) +
                            " and SerialNumber = " + SqlString(dr["SerialNumber"].ToString().Trim());
                        object obj = cmd.ExecuteScalar();
                        if (obj != null)
                        {
                            cmd.CommandText = "UPDATE InventoryTransfer_Master " +
                                "SET Completed = 0 WHERE DocumentNumber = " + SqlString(obj.ToString());
                            cmd.ExecuteNonQuery();

                            cmd.CommandText = "UPDATE InventoryTransfer_Serial " +
                                "SET OriginalBin = '', NewBin = '' " +
                                "WHERE DocumentNumber = " + SqlString(obj.ToString()) + " and SerialNumber = " + SqlString(dr["SerialNumber"].ToString().Trim());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }

                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

            //this.Close();
        }
        catch (Exception ex)
        {
            ELog el = new ELog(ex, CLASS_NAME + " revertSave");
        }
    }

Answer 1

如果不实际尝试就很难预测复杂系统的性能，但存储过程很可能会提高执行速度，尤其是在数据集很大的情况下。

用你现在的方法你有

• 每行四个 SQL 命令。因此，对于 1,000 行，将有 4,000 个命令。
• 4,000 次网络跃点
• 4,000 个单行锁实例
• 4,000 个 SQL 编译事件

使用存储过程，理论上您可以

• 总共三个 SQL 命令，它们将获取必要值所需的所有行关联起来。虽然这些语句会非常复杂(有很多连接)，但 3 远少于 4,000。
• 一个网络跃点
• 一个事务，包含所有必要的行、页或潜在的表锁。
• 如果存储过程提前编译，则完全没有编译事件。

使用存储过程，一切看起来都好得多，除了更大锁定的可能性。根据您的整体解决方案，该锁可能会破坏交易，因为它可能会阻止其他用户同时尝试执行相同操作的其他进程。

还有一些令人毛骨悚然的细节——例如，如果表很大并且有很多行您没有更新，那么一次更新一行可能比进行批量更新更有效，并且批量更新会触发表扫描而不是索引查找。但是，优秀的 SQL 开发人员可以定制 SQL 查询和命令，以将 SQL Server 推向正确的方向。

TLDR:在大多数情况下，存储过程会给您带来更好的性能。