SqlCommand.Prepare questions RRS feed

  • Question

  • What does SqlCommand.Prepare specifically do (the MSDN docs are vague)?  What benefit with SQL2008 (if any) does SqlCommand.Prepare offer for :

    1. Insert statements

    2. Select queries

    We typically use SqlCommand objects to invoke stored procedures or to execute parameterized SQL queries / statements (avoid Adhoc).




    Friday, January 6, 2012 5:38 AM


  • Internally it will call sp_prepare to setup a "prepared" statement handle with SQL Server.   The handle is used to reference the parameters collection and prepared execution plan on the server side.  Using the handle is slightly faster than having SQL Server locate the cached query plan for you on the fly using the signature of the incoming request.

    To explain in more detail:  When you send a parameterized query (stored procedure or whatever) to SQL Server, SQL Server will take the query along with supplied parameters and create a plan for executing the query.  Creating the plan can often be more expensive than running the query.  The plan is calculated to be the most efficient way to run the query (which tables to fetch data from first, etc...).   The plan also considers the parameters supplied as well as values supplied to the parameters.

    Once the plan is created, it is cached in memory to avoid having to create it again.  The idea is you may run the same query later so this saves CPU cycles re-creating the plan.

    With SqlCommand.Prepare, what the driver does is tell SQL Server here is a query and parameters, please compile the query and create the plan and store it somewhere for me an give me a handle for this plan.   The SQL Server prepares the statement etc and returns back to the client the handle.  Later when you call SqlCommand.Execute, it passes the handle and the parameter values and SQL can quickly and efficiently locate the exact matching plan for the query.

    If you do not cal SqlCommand.Prepare, no plan handle is ever created and passed back to client, but a plan is still created and cached on the SQL Server side.  The server locates the plan by hashing the query string plus parameters.   So it is slightly more expensive to hash the query string and parameters to locate the plan than just pass a handle to SQL.

    You can read more in depth about this plan cache here, it gets pretty complicated =>

    The difference between using plan cache properly or not with SQL Server can be huge, so it is worth understanding it a bit if you really want high performance queries.  You can start understanding it by using the SQL Profiler tool, it shows you alot of what is going on, if you are hitting or missing the cache, query compile time, etc...




    Friday, January 6, 2012 7:33 PM