C#-SQL：如何执行一批StoredProcedure？

Question

编辑：
我的问题不再是问题了：我重做了我的性能测试，并且犯了一个致命的愚蠢错误：我忘记了 x1000 从毫秒中获取秒：/ 对不起那些家伙。
欲了解信息：
- 我每秒从我的 PC 到本地网络上的数据库服务器进行大约 1900 次更新。
- 如果程序与数据库位于同一台机器上，则每秒更新 3.200 次。
- 我的电脑在数据库服务器上每秒进行 3.500 次更新，我不会重新创建并重新打开新的 SQLConnection。
- 批量文本每秒更新 5.800 次。 对于我的 10,000 行，如果需要 5 秒，那么对于我的程序来说是可以的。 抱歉让您担心了。

实际上，我使用 SQL 存储过程在数据库中创建一行以避免 SQL 注入。 在 C# 中，我有以下方法：

public void InsertUser(string userCode)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      sqlCommand.Parameters.Add(new SqlParameter("@UserCode", userCode));
      sqlConnection.Open();
      sqlCommand.ExecuteNonQuery();///0.2 seconds !ERROR HERE! 0.2ms here,NOT 0.2sec!!!
   }
} 

当我要插入一两行时，它非常有用。 但如果我需要创建 1000 个用户、10000 个产品和 5000 只宠物，这不是最好的解决方案：我将在网络传输上浪费大量时间。

我相信，在不签入的情况下，我只能使用有限数量的回调。 所以我不想调用 10.000 次：

sqlCommand.BeginExecuteNonQuery()

另一种方法是创建批处理文本，但存在 SQL 注入风险（而且很难看）。

.Net 中是否有一个“SqlCommandList”对象来管理它？ 如何在数据库中进行大量写入？ 有什么好的模式呢？

Answer 1

这应该运行得更快一点：

public void InsertUser(IEnumerable<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString), 
             SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection))
   {
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
      sqlConnection.Open();

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();///0.2 seconds
      }
   }
}

即使您传递 1000 个用户，它也只会打开一个连接并只创建一个命令。 不过，它仍然会单独执行每个插入。 当然，如果 userCode 不是字符串，您将需要适当地重构它。 您可能还想了解 SQL Server 的 BULK INSERT 命令。

Answer 2

SQLDataAdaptor 的 UpdateBatchSize 怎么样？

我们的前端人员使用它来将几个 10,000 个过程调用批处理成块

文章

MSDN

我们的环境不允许“bulkadmin”权限，因此我们无法使用 BULKINSERT/bcp 等

Answer 3

就我个人而言，如果我经常期望进行相当大的插入（10,000 行肯定符合条件......），我可能会考虑为传入数据建立一个单独的表，并使用 SqlBulkCopy 填充此表。 然后，您只需执行一个存储过程，将数据移至真实表中。

另一种方法是将 xml 发送到数据库，并使用 sqlxml 对其进行解析（使用 SQL2005 及更高版本更容易） - 但这会给数据库服务器带来额外的工作。

Answer 4

如果你真的担心这一点，你可以（就像你说的）将命令批量放入字符串中，如下所示：

var cmd = new SqlCommand();
cmd.Connection = sqlConnection;
for (int i = 0; i < batchSize; i++) {
    cmd.CommandText += String.Format("EXEC InsertUser @UserCode{0};", i);
    cmd.Parameters.AddWithValue("@UserCode" + i.ToString(), XXXXX);
    //... etc ...
}

因为在这个方案中，你将使用一个参数，所以你不会比你有更多的 SQL 注入风险使用存储过程。 但我怀疑这样做是否真的能节省大量时间。 在我看来，你应该保持简单并按照你现在的方式去做。

Answer 5

根据 Joel 的回答，这是除了使用 SqlBulkCopy 或创建大串混乱的 SQL 并执行之外最快的解决方案。 （我添加了一个事务，这将大大提高性能）

public void InsertUser(IEnumerabler<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      sqlConnection.Open();
      SqlTransaction transaction = connection.BeginTransaction();
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.Transaction = transaction;
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();
      }      
      transaction.Commit();
   }
}

Answer 6

我猜这是一个很老的问题。

对于 SQL Server 2008，现在的答案是使用表值参数。 简而言之，将所有变量传递到使用的定义类型（表）中。

在 SQL 中，您现在可以将所有记录作为单独的项目进行处理...实际上使用集合逻辑并获得真正的性能。

Answer 7

您是否考虑过将 XML 文档传递给存储过程，然后迭代该过程以查找要插入的数据？

Answer 8

“这不是最好的解决方案：我将在网络传输中浪费大量时间”
你能忍受损失吗？

如果这是您不经常做的事情，那么这有什么关系吗？
首先测量它，如果有问题，然后解决它，就我个人而言，我可能会使用 Marc Gravells 表来处理传入的插入件。
另一种选择是异步触发插入，这样您就不必等待每个插入完成后再开始下一个插入。

我花了很多年的时间，但最终我发现我不应该浪费时间来优化不需要它的代码。

希望这会有所帮助（尽管我认为不会，抱歉）。

Answer 9

根据上面的一些答案，以最少的努力实现最显着的性能提升涉及对现有代码进行 2 项更改：

1. 将更新包装在事务中
2. 仅打开一个连接并使用不同的参数多次调用该过程。

批量插入是一种选择，但对于您想做的事情来说可能有点过分了。