Preparing SQL Statements

The Microsoft® SQL Server™ 2000 relational engine introduces full support for preparing SQL statements before they are executed. If an application needs to execute an SQL statement several times, using the database API it can:

• Prepare the statement once. This compiles the SQL statement into an execution plan.
  
  
• Execute the precompiled execution plan each time it needs to execute the statement. This saves recompiling the SQL statement on each execution after the first.

  Preparing and executing statements is controlled by API functions and methods. It is not a part of the Transact-SQL language. The prepare/execute model of executing SQL statements is supported by the Microsoft OLE DB Provider for SQL Server and the SQL Server 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 to 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 associated with the handle.

Prepared statements cannot be used to create temporary objects on SQL Server 2000 or SQL Server version 7.0. 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 an application is asked occasionally to retrieve product information from the Northwind sample database. There are two methods for how the application can do this.

In the first method, the application could execute a separate query for each product requested:

SELECT * FROM Northwind.dbo.Products
WHERE ProductID = 63

An alternative would be for the application to:

1. Prepare a statement containing a parameter marker (?):

   SELECT * FROM Northwind.dbo.Products
   WHERE ProductID = ?
   

2. Bind a program variable to the parameter marker.
   
   
3. Each time product information is needed, fill the bound variable with the key value and execute the statement.

The second method is more efficient when the statement is executed more than three times.

In SQL Server 2000, the prepare/execute model has little performance advantage over direct execution because of the way SQL Server 2000 reuses execution plans. SQL Server 2000 has efficient algorithms for matching current SQL statements with execution plans generated for prior executions of the same SQL statement. If an application executes an SQL statement with parameter markers multiple times, SQL Server 2000 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 offers 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 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 did not support the prepare/execute model directly. The SQL Server ODBC driver, however, supported the prepare/execute model by using stored procedures:

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

In SQL Server 6.5 and SQL Server 6.0, the generated stored procedures were temporary stored procedures stored in tempdb. SQL Server version 4.21a and earlier did not support temporary stored procedures, so the driver generated regular stored procedures stored in the current database. The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver included with SQL Server 2000 follows these behaviors when connected to SQL Server version 6.5, SQL Server version 6.0, and SQL Server version 4.21a.

See Also

Execution Plan Caching and Reuse

Parameters and Execution Plan Reuse

Executing Prepared Statements

Preparing Commands

Prepared Execution