Preparing SQL Statements

  • Article

The SQL Server 2005 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 Native Client OLE DB Provider and the SQL 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 2005. 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:

  1. Prepares a statement that contains a parameter marker (?):

    SELECT * FROM AdventureWorks.Production.Product
WHERE ProductID = ?

  2. Binds a program variable to the parameter marker.

  3. 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 2005, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL Server 2005 reuses execution plans. SQL Server 2005 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 2005 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.

Prepare and Execute in Earlier Versions of SQL Server

SQL Server version 6.5 and earlier do not support the prepare/execute model directly. However, the SQL Server ODBC driver supports the prepare/execute model by using stored procedures:

  • When an application requests that an SQL statement be prepared, the ODBC driver wraps the SQL statement in a CREATE PROCEDURE statement and sends it to SQL Server.
  • On an execute request, the ODBC driver requests that SQL Server execute the generated stored procedure.

In SQL Server 6.5 and SQL Server 6.0, the generated stored procedures are temporary stored procedures stored in tempdb. Because SQL Server version 4.21a and earlier do not support temporary stored procedures, the driver generates regular stored procedures that are stored in the current database. The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver included with SQL Server 2000 follow this behavior when connected to SQL Server version 6.5, SQL Server version 6.0, and SQL Server version 4.21a.

See Also

Concepts

Execution Plan Caching and Reuse
Parameters and Execution Plan Reuse

Help and Information

Getting SQL Server 2005 Assistance