如何在C# .NET（SQL Server）中正确高效地重用预处理语句？

Question

我看了很多问题，但显然我的“SO-fu”还不够强，所以我来这里了。我试图有效地使用准备好的语句，我不仅仅是指将单个语句参数化，而是为重复使用编译一个语句。我的问题在于参数和重用以及如何正确实现它。

通常我遵循以下过程（虚构的例子）：

SqlConnection db = new SqlConnection(...);
SqlCommand s = new SqlCommand("select * from foo where a=@a", db);
s.Parameters.Add("@a", SqlDbType.VarChar, 8);
s.Prepare();
...
s.Parameters["@a"] = "bozo";
s.Execute();

超级，这很有效。然而，我不想每次运行此查询时都执行所有这些步骤（或后四个步骤）。这似乎与准备语句的整个思想相反。在我看来，我只需要更改参数并重新执行即可，但问题是如何做到这一点？

我尝试过 s.Parameters.Clear()，但这实际上会删除参数本身，而不仅仅是值，因此我基本上需要重新添加参数并重新准备，这似乎也破坏了整个目的。谢绝。

现在，我只能遍历 s.Parameters 并将它们全部设置为 null 或其他某个值。这样做正确吗？不幸的是，在我的当前项目中，我有大约 15 个参数的查询需要执行 ~10,000 次。我可以将这个迭代推到一个方法中，但是想知道是否有更好的方法来解决这个问题（没有存储过程）。

我的当前解决方法是创建一个扩展方法 SqlParameterCollection.Nullify，将所有参数设置为 null，这对我的情况来说很好。我只需在执行后运行此方法。

---

我找到了一些几乎完全相同但（在我看来）没有回答的问题：

.NET中的预处理语句和内置连接池

SQLite/C#连接池和预处理语句混淆（Serge已经非常接近回答了！）

我能找到的最好的答案是（1）常识优先，以及（2）这个页面：

http://msdn.microsoft.com/en-us/magazine/cc163799.aspx

- Josh

---

奇怪，我在编辑框的第一个链接后面加了一个换行符，但它没有出现在呈现的输出中... 已修复。 - Josh

将其放在一个方法中，将SqlParameters集合传递给它并重复使用其余部分。 :) - Marciano.Andrade

阅读此文章http://msdn.microsoft.com/en-us/magazine/ee236412.aspx，确保准确定义参数的大小。这应该有助于提高性能。（当然除了存储过程） - Steve

你是在一个紧密的循环中执行吗？否则，我会怀疑你可能处于“Prepare（）”理想的情况下。据我了解，“Prepare（）”要有用，你必须保留你的“SqlCommand”对象，这可能会导致在最终完成时处理它时出现问题。 - Sven Grosen

@Josh，我建议你不要担心使用Prepare()，每次都创建SqlCommand，除非你可以重构你的逻辑，使其在单个存储过程调用中完成所有操作。 - Sven Grosen

Answer 1

当重复使用已经准备好的 SqlCommand 时，你只需要将参数值设置为新值即可，无需在使用后清除它们。

就我自己而言，在过去的十年中，我没有看到任何一个 DBMS 可以从准备语句中获得明显的好处（我想，如果 DB 服务器处于其 CPU 的极限状态，可能会有好处，但这并不是典型情况）。你确定需要进行准备吗？

每次运行“~10,000 次”相同的命令对我来说有点奇怪，除非你正在从外部源上传。在这种情况下，批量加载可能会有所帮助？每次运行都在做什么？

- simon at rcl

---

这基本上是来自各种来源（SQL、LDAP、SOAP等）的周期性外部负载，需要进行处理以将传入数据转换为新结构。不幸的是，它比列映射或选择/插入更复杂。我在其他地方看到过其他答案强烈推荐准备，但在阅读了大量在线资料后，我并不相信大多数人正确使用准备语句（用于此目的）。清除它们只是一种预防措施，问题实际上是重置参数是否是保留已编译语句的正确方法。 - Josh

谢谢。您只需要将参数重置为新值（我刚测试过，它可以正常工作）。您不需要将它们设置为 null 或其他任何值。因此，是的，请保持连接打开，保留 SqlCommands，并根据需要重置参数值。 - simon at rcl

感谢注意保持连接开放。最终我创建了一个静态的DB类，并向其“注册”命令，以便在整个导入过程中只有一个连接可以重复使用这些命令。幸运的是，这是一个单线程应用程序。我还确保在执行已注册的命令时必须提交参数，并确保它们全部存在，以防止之前的值残留（我发现它们在执行之间会保留）。 - Josh

Answer 2

补充Simon的回答，在Sql 2005之前，Command.Prepare() 可以提高adhoc查询缓存的查询计划（SPROCs通常会被编译）。然而，在更新的Sql版本中，只要你的查询是参数化的，也可以缓存一些参数化的adhoc查询，减少了对Prepare()的需要。

以下是一个示例，保留了SqlParameters集合的同时更改那些不同的参数值，以防止重复创建参数（即节省参数对象创建和收集）：

using (var sqlConnection = new SqlConnection("connstring"))
 {
    sqlConnection.Open();
    using (var sqlCommand = new SqlCommand
       {
          Connection = sqlConnection,
          CommandText = "dbo.MyProc",
          CommandType = CommandType.StoredProcedure,
       })
    {
       // Once-off setup per connection
       // This parameter doesn't vary so is set just once
       sqlCommand.Parameters.Add("ConstantParam0", SqlDbType.Int).Value = 1234;
       // These parameters are defined once but set multiple times
       sqlCommand.Parameters.Add(new SqlParameter("VarParam1", SqlDbType.VarChar));
       sqlCommand.Parameters.Add(new SqlParameter("VarParam2", SqlDbType.DateTime));

       // Tight loop - performance critical
       foreach(var item in itemsToExec)
       {
         // No need to set ConstantParam0
         // Reuses variable parameters, by just mutating values
         sqlParameters["VarParam1"].Value = item.Param1Value; // Or sqlParameters[1].Value
         sqlParameters["VarParam2"].Value = item.Param2Date; // Or sqlParameters[2].Value
         sqlCommand.ExecuteNonQuery();
       }
    }
}

注：

• 如果您要插入大量的行，并且与数据库中其他用户的并发性很重要，而且 ACID 事务边界不重要，那么您可以考虑批处理和提交更新，这样每次在表上保留的行锁将少于5000个，以防止表锁升级。
• 根据您的存储过程实际执行的工作，可能有机会并行化循环，例如使用 TPL。显然，连接和命令不是线程安全的，因此每个任务都需要自己的连接和可重用命令- Parallel.ForEach 的 localInit 重载对此非常理想。

- StuartLC