14

I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of this?

Sample:

command.Prepare();
command.ExecuteNonQuery();
//...
command.Parameters[0].Value = 20;
command.ExecuteNonQuery();

I have played around a little bit and traced. The execution of the Command after calling the Prepare() method makes Sql Server execute the following statement:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@id int,@desc text',N'INSERT INTO dbo.testtable (id) VALUES (@id)',@id=20'
select @p1

After that when the Parameter gets it's value and SqlCommand.ExecuteNonQuery() is called, the following gets executed on Sql-Server:

exec sp_execute 1,@id=20

To me this looks like the statement gets kind of compiled as soon Prepare() is executed. I wonder what is the benefit of this? Does this mean that it is put into the plan cache and can be re-used as soon the final query is executed with the desired parameter values?

I figured out (and documented it in another question) that SqlCommands that are executed with SqlParameters always are wrapped in sp_executesql procedure calls. This makes Sql Server able to store and reuse the plans independant of the parameter values.

Regarding this I wonder if the prepare() method is kind of useless or obsolete or if I am missing something here?

  • I always thought it had something to do with preventing SQL Injection, so you'd prepare the query to accept certain variables of certain types. That way if you don't pass in variables of those types, the query fails – Mark Sinkinson Feb 18 '16 at 11:45
  • Although in saying that, this PHP explanation is probably just as valid for .NET php.net/manual/en/pdo.prepared-statements.php – Mark Sinkinson Feb 18 '16 at 11:48
  • "Yes, sir. Driver, prepare to move out." "What are you preparing?! You're always preparing! Just go!" – Jon of All Trades Feb 22 '16 at 20:55
20

Preparing a SQL batch separately from executing the prepared SQL batch is a construct that is effectively** useless for SQL Server given how execution plans are cached. Separating out the steps of preparation (parsing, binding any parameters, and compiling) and execution only makes sense when there is no caching. The purpose is to save the time spent on parsing and compiling by reusing an existing plan, and this is what the internal plan cache does. But not all RDBMS's do this level of caching (clearly from the existence of these functions) and so it is left up to the client code to request that a plan be "cached", and so save that cache ID, and then re-use it. This is additional burden on the client code (and programmer to remember to do it and get it right) that is unnecessary. In fact, the MSDN page for the IDbCommand.Prepare() method states:

The server automatically caches plans for reuse as necessary; therefore, there is no need to call this method directly in your client application.

It should be noted that, as far as my testing shows (which matches what you show in the Question), calling SqlCommand.Prepare(), does not do a "prepare"-only operation: it calls sp_prepexec which prepares and executes the SQL; it does not call sp_prepare which is parse and compile only (and does not take in any parameter values, only their names and datatypes). Hence, there cannot be any "pre-compile" benefit of calling SqlCommand.Prepare since it does an immediate execution (assuming the goal is to defer execution). HOWEVER, there's an interesting potential minor benefit of calling SqlCommand.Prepare(), even if it does call sp_prepexec instead of sp_prepare. Please see the note (**) at the bottom for details.

My testing shows that even when SqlCommand.Prepare() is called (and please note that this call does not issue any commands on SQL Server), the ExecuteNonQuery or ExecuteReader that follows is fully executed, and if it is ExecuteReader, it returns rows. Still, SQL Server Profiler does show that the first SqlCommand.Execute______() call after the SqlCommand.Prepare() call registers as a "Prepare SQL" event, and subsequent .Execute___() calls register as "Exec Prepared SQL" events.


Test Code

It has been uploaded to PasteBin at: http://pastebin.com/Yc2Tfvup. The code creates a .NET / C# Console App that is intended to run while a SQL Server Profiler trace is running (or an Extended Events session). It pauses after each step so it will be clear which statements have a particular effect.


UPDATE

Found more info, and a slight potential reason to avoid calling SqlCommand.Prepare(). One thing I noticed in my testing, and what should be noted for anyone running that test Console App: there is never an explicit call made sp_unprepare. I did some digging and found the following post in the MSDN forums:

SqlCommand - Prepare or not to prepare?

The accepted answer contains a bunch of info, but the salient points are:

  • ** What prepare actually saves you is primarily the time it takes to transmit the query string over the wire.
  • A prepared query has no guarantee of a cached plan being saved, and is no faster than an ad-hoc query once it reaches the server.
  • RPC's (CommandType.StoredProcedure) gain nothing from preparing.
  • On the server, a prepared handle is basically an index into an in-memory map containing TSQL to execute.
  • The map is stored on a per-connection basis, no cross-connection use is possible.
  • The reset sent when the client re-uses the connection from the pool clears the map.

I also found the following post from the SQLCAT team, which appears to be related, but could simply be an issue specific to ODBC whereas SqlClient does clean up correctly upon disposing of the SqlConnection. It is hard to say since the SQLCAT post does not mention any additional tests that would help prove this as a cause, such as clearing the connection pool, etc.

Watch out those prepared SQL statements


CONCLUSION

Given that:

  • the only real benefit of calling SqlCommand.Prepare() seems to be that you don't need to submit the query text again over the network,
  • calling sp_prepare and sp_prepexec store the query text as part of the connection's memory (i.e. SQL Server memory)

I would recommend against calling SqlCommand.Prepare() because the only potential benefit is saving network packets while the downside is taking up more server memory. While the amount of memory consumed is probably very small in the majority of cases, network bandwidth is rarely ever an issue since most DB servers are directly connected to the app servers over 10 Megabit minimum (more likely 100 Megabit or Gigabit these days) connections (and some are even on the same box ;-). That and memory is nearly always a more scarce resource than network bandwidth.

I suppose, for anyone writing software that can connect to multiple databases, then the convenience of a standard interface might change this cost/benefit analysis. But even in that case, I have to believe that it is still easy enough to abstract a "standard" DB interface that allows for different providers (and hence differences between them, such as not needing to call Prepare() ) given that doing so is basic Object Oriented programming that you are likely already doing in your app code ;-).

| improve this answer | |
  • Thank you for this extensive answer. I tested your steps and had two aberrations from your expectations/results: in step 4 I got an additional result. In step 10 I got another plan_handle than in step 2. – Magier Feb 22 '16 at 14:53
  • 1
    @Magier Not sure about step 4...maybe you had different SET options or somehow the query text between the two was different? Even a single character (visible or not) difference will be a different plan. Copy and paste from this web page might not help, so copy the query (everything between the single quotes) from the sp_prepare into the call to sp_executesql, just to be sure. For step 10, yeah, I did more testing yesterday and did see some occasions with different handles for sp_prepare, but still never the same for sp_executesql. I will test one more thing and update my answer. – Solomon Rutzky Feb 22 '16 at 16:46
  • 1
    @Magier Actually, the test I ran before covered it and I don't think there is any true re-use of the plan, especially in light of that MSDN forum post saying that it caches just the SQL. I am still curious how it is able to re-use the plan_handle whereas sp_executesql cannot or does not. But regardless, the parse time is still there on sp_prepare if the plan has been removed from the cache so I will remove that part of my answer (interesting but irrelevant). That part was more of an interesting side note anyway since it didn't address your direct question. Everything else still applies. – Solomon Rutzky Feb 22 '16 at 20:16
  • 1
    This was the kind of explanation i was looking for. – CSharpie Mar 11 '16 at 7:58
5

Regarding this I wonder if the prepare() method is kind of useless or obsolete or if I am missing something here?

I would say that the Prepare method has limited value SqlCommand world, not that it's entirely useless. One benefit is that Prepare is part of the IDbCommand interface that SqlCommand implements. This allows the same code to run against other DBMS providers (that may require Prepare) without conditional logic to determine if Prepare should be called or not.

Prepare has no value with the .NET Provider for SQL Server besides this use cases, AFAIK.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.