Preparing SQL Statements
The SQL Server relational engine introduces full support for preparing SQL statements before they are executed. If an application has to execute an SQL statement several times, it can use the database API to do the following:
Prepare the statement once. This compiles the SQL statement into an execution plan.
Execute the precompiled execution plan every time it has to execute the statement. This prevents having to recompile the SQL statement on each execution after the first time.
Preparing and executing statements is controlled by API functions and methods. It is not part of the Transact-SQL language. The prepare/execute model of executing SQL statements is supported by the SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver. On a prepare request, either the provider or the driver sends the statement to SQL Server with a request to prepare the statement. SQL Server compiles an execution plan and returns a handle for that plan to the provider or driver. On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.
Prepared statements cannot be used to create temporary objects on SQL Server. Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.
Excess use of the prepare/execute model can degrade performance. If a statement is executed only once, a direct execution requires only one network round-trip to the server. Preparing and executing an SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.
Preparing a statement is more effective if parameter markers are used. For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. There are two ways the application can do this.
Using the first way, the application can execute a separate query for each product requested:
SELECT * FROM AdventureWorks.Production.Product
WHERE ProductID = 63;
Using the second way, the application does the following:
Prepares a statement that contains a parameter marker (?):
SELECT * FROM AdventureWorks.Production.Product WHERE ProductID = ?;Binds a program variable to the parameter marker.
Each time product information is needed, fills the bound variable with the key value and executes the statement.
The second way is more efficient when the statement is executed more than three times.
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.