SqlCommand.Prepare() 做什么以及何时使用? [英] What does SqlCommand.Prepare() do and when should it be used?

问题描述

可能重复:
在 C# 中使用 SqlCommand Prepare 的优缺点?

这是 MSDN 所说的 关于SqlCommand.Prepare():

创建一个准备好的版本SQL Server 实例上的命令.

Creates a prepared version of the command on an instance of SQL Server.

谁能提供更多关于这意味着什么以及何时应该使用它的见解?

Can anybody provide more insight as to what that means and when it should be used?

推荐答案

Prepare 方法实际上是在 DbCommand 上，所有从它派生的类都会选择该方法.

The Prepare method is actually on DbCommand, which all classes which derive from it will pick up.

它的作用特定于 DbCommand 所针对的数据库提供程序.但是，可以肯定地说(尽管不是绝对规则)，在大多数地方，如果命令是一个存储过程，它将产生一个 no op(它被记录为 覆盖 SqlCommand 上的 Prepare)，如存储过程通常会因为之前的调用、显式调用优化或创建时(同样取决于底层数据库)而优化其查询计划.

What it does is specific to the database provider that the DbCommand is for. However, it's safe to say (although not an absolute rule) that in most places, if the command is a stored procedure, it will produce a no op (it is documented as such for the override of Prepare on SqlCommand), as stored procedures typically have their query plans optimized because of prior calls, explicit calls to optimize, or on creation (again, depending on the underlying database).

但是，如果您不使用存储过程，而是使用动态生成的参数化查询，那么此调用将为底层数据库提供生成优化版本的机会查询.

However, if you are not using a stored procedure, but rather a parameterized query generated on-the-fly, then this call will give the underlying database the opportunity to generate an optimized version of the query.

当您知道要在短时间内多次执行命令时，通常会执行此操作(实际上，这取决于数据库以及查询计划的缓存时间).

You would typically do this when you know you are going to execute the command multiple times within a short amount of time (it depends, really, on the database, and how long the query plans are cached).

应该指出，SQL Server(截至 2005 年，IIRC)根据第一次执行后的使用情况缓存参数化查询计划(我认为缓存是时间降级的缓存，它会重置或在随后的使用)，因此如果您要使用相同的参数化查询进行多次调用，那么调用 Prepare 可能不会获得太多收益，而不是提前移动查询准备工作(这可能是一个好处以及，取决于您必须执行的工作).

It should be stated that SQL Server (as of 2005, IIRC) caches parameterized query plans depending on usage after the first execution (I think the cache is a time-degraded cache which resets or has its rate of decay slowed on subsequent uses), so if you are going to make multiple calls with the same parameterized query, then you might not gain much with a call to Prepare other than moving the query preparation work upfront (which might be a benefit as well, depending on what the work is you have to perform).

这篇关于SqlCommand.Prepare() 做什么以及何时使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！