Preparing SQL Statements

The Microsoft® SQL Server™ version 7.0 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 OLE DB Provider for SQL Server and the SQL Server ODBC driver. On a prepare request, the provider or 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, the provider or driver sends the server a request to execute the plan associated with the handle.

Excess use of the prepare/execute model can degrade performance. If a statement is only executed once, a direct execution requires only one network round trip to the server. Preparing and then executing an SQL statement only executed one time requires an extra network round-trip; one to prepare the statement and one to execute it.

Preparing a statement is more effective if parameter markers are used. For example, assume an application may be asked occasionally to retrieve product information from the Northwind sample database. There are two ways the application can do this. First, 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 or four times.

In SQL Server 7.0, the relative performance advantage of the prepare/execute model over direct execution is reduced by the reuse of execution plans. SQL Server 7.0 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 7.0 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 slightly 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

Versions of SQL Server earlier than 7.0 did not support the prepare/execute model directly. The SQL Server ODBC driver, however, supported the prepare/execute model through the use of 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 that to SQL Server.
• On an execute request, the ODBC driver would request that SQL Server execute the generated stored procedure.

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

See Also