What does SqlCommand.Prepare() do and when should it be used? [duplicate]

Question

Possible Duplicate:
Pros and Cons of using SqlCommand Prepare in C#?

This is what MSDN says about SqlCommand.Prepare():

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?

Purington

Related question #2450327 – Sarena

Answer 1

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

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).

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).

Fitzpatrick

When you use Stored Procedures multiple times with different parameters, I use the Prepare method. If you just change the parameters, the result will still be that of the first set of parameters. I don't know if this is the best way to do it, but anyway this method is not a no-op when used on a stored procedure. (The documentation says that it can be, not that it is a no-op.) – Turret

Answer 2

Much more information can be found here.

However, bear in mind:

In SQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL Server reuses execution plans. SQL Server has efficient algorithms for matching current SQL statements with execution plans that are generated for prior executions of the same SQL statement. If an application executes a SQL statement with parameter markers multiple times, SQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the procedure cache). The prepare/execute model still has these benefits:

Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an SQL statement to existing execution plans.

The application can control when the execution plan is created and when it is reused.

The prepare/execute model is portable to other databases, including earlier versions of SQL Server.

Offer

Answer 3

Normally when you execute a query, it's taken all the way from parsing the string to running the execution plan. By calling Prepare, it will take the process as far as possible towards the execution, without actually running the execution plan.

This is useful when running the same command over and over. You will save some execution time, as the whole process doesn't have to be repeated each time.

Matthia