Prepared Execution
The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Microsoft® SQL Server™ statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.
For most databases, prepared execution is faster than direct execution for statements executed more than three or four times primarily because the statement is compiled only once, while statements executed directly are compiled each time they are executed. Prepared execution can also provide a reduction in network traffic because the driver can send an execution plan identifier and the parameter values, rather than an entire SQL statement, to the data source each time the statement is executed.
SQL Server 2000 reduces the performance difference between direct and prepared execution through improved algorithms for detecting and reusing execution plans from SQLExecDirect. This makes some of the performance benefits of prepared execution available to statements executed directly. For more information, see Direct Execution.
SQL Server 2000 also provides native support for prepared execution. An execution plan is built on SQLPrepare and later executed when SQLExecute is called. Because SQL Server 2000 is not required to build temporary stored procedures on SQLPrepare, there is no extra overhead on the system tables in tempdb.
For performance reasons, the statement preparation is deferred until SQLExecute is called or a metaproperty operation (such as SQLDescribeCol or SQLDescribeParam in ODBC) is performed. This is the default behavior. Any errors in the statement being prepared are not known until the statement is executed or a metaproperty operation is performed. Setting the SQL Server ODBC driver-specific statement attribute SQL_SOPT_SS_DEFER_PREPARE to SQL_DP_OFF can turn off this default behavior.
In case of deferred prepare, calling either SQLDescribeCol or SQLDescribeParam before calling SQLExecute generates an extra roundtrip to the server. On SQLDescribeCol, the driver removes the WHERE clause from the query and sends it to the server with SET FMTONLY ON to get the description of the columns in the first result set returned by the query. On SQLDescribeParam, the driver calls the server to get a description of the expressions or columns referenced by any parameter markers in the query. This method also has some restrictions, such as not being able to resolve parameters in subqueries.
Excess use of SQLPrepare with the SQL Server ODBC driver degrades performance, especially when connected to earlier versions of SQL Server. Prepared execution should not be used for statements executed a single time. Prepared execution is slower than direct execution for a single execution of a statement because it requires an extra network roundtrip from the client to the server. On earlier versions of SQL Server it also generates a temporary stored procedure.
Prepared statements cannot be used to create temporary objects on SQL Server 2000, or on earlier versions of SQL Server if the option to generate stored procedures is active. With this option turned on, the prepared statement is built into a temporary stored procedure that is executed when SQLExecute is called. Any temporary object created during the execution of a stored procedure is automatically dropped when the procedure finishes. Either of the following examples results in the temporary table #sometable not being created if the option to generate stored procedures for prepare is active:
SQLPrepare(hstmt,
"CREATE TABLE #sometable(cola int, colb char(8))",
SQL_NTS);
SQLExecute(hstmt);
or
SQLPrepare(hstmt,
"SELECT * FROM authors INTO #sometable",
SQL_NTS);
SQLExecute(hstmt);
Some early ODBC applications used SQLPrepare anytime SQLBindParameter was used. SQLBindParameter does not require the use of SQLPrepare, it can be used with SQLExecDirect. For example, use SQLExecDirect with SQLBindParameter to retrieve the return code or output parameters from a stored procedure that is only executed one time. Do not use SQLPrepare with SQLBindParameter unless the same statement will be executed multiple times.
SQLPrepare on SQL Server version 6.5 or earlier
Earlier versions of SQL Server did not directly support prepared execution. To get the benefits of prepared execution on earlier versions of SQL Server, the SQL Server ODBC driver uses temporary stored procedures. On SQLPrepare, the SQL Server ODBC driver builds the SQL statement from the application into a CREATE PROCEDURE statement that it then sends to the server. This creates a temporary stored procedure and is essentially the same as having SQL Server parse the SQL statement and compile it into an execution plan. The names of the temporary stored procedures generated by the SQL Server ODBC driver start with #odbc#. On SQLExecute, the driver calls the stored procedure created on SQLPrepare. Administrators of SQL Server versions 6.0 or 6.5 must estimate the peak demand for SQLPrepare and make tempdb large enough to hold these temporary stored procedures.
SQL Server version 4.21a does not support temporary stored procedures. When connected to SQL Server 4.21a the SQL Server ODBC driver generates permanent stored procedures instead of temporary stored procedures. These permanent stored procedures are stored in the user databases, so administrators must ensure the user databases are large enough to hold the peak number of SQLPrepare functions. Also, the permanent stored procedures can be left in the database if the application terminates or loses its connection before the ODBC driver can drop the procedures. SQL Server 4.21a administrators may be required to periodically drop these stored procedures.
If an application will be run by many concurrent users and the users will all be using the same SQL statement, the best approach is to create the SQL statement as a permanent, parameterized stored procedure, and execute it with SQLExecDirect. Having many users issue concurrent SQLPrepare commands on earlier versions of SQL Server can create a concurrency problem on the system tables in tempdb. Even if each user is executing exactly the same statement, the SQL Server ODBC driver on each client is creating its own copy of a temporary stored procedure in tempdb. If the SQL statement is created as a parameterized stored procedure, however, the procedure is created only once. Each ODBC application does not have to create a new procedure for its exclusive use. It simply uses a copy of the execution plan of the permanent procedure from the procedure cache.
To avoid holding locks on tempdb system tables for the length of a user transaction, the SQL Server ODBC driver does not generate a stored procedure for SQLPrepare if it is called within a transaction. The exception to this is when the SQLPrepare is the first statement in the transaction. In this case, the driver generates a stored procedure but then immediately commits the CREATE PROCEDURE statement.
The driver does not generate a stored procedure for a SQLPrepare that uses the ODBC CALL escape clause to call a stored procedure. On SQLExecute, the driver executes the called stored procedure. (Creating a temporary stored procedure is not required.)
Whether the SQL Server ODBC driver generates temporary stored procedures when connected to earlier versions of SQL Server, and how long the procedures are retained, is controlled by data source parameters or connection attributes. The connection attributes are set by calling SQLSetConnectAttr with fOption set to SQL_COPT_SS_USE_PROC_FOR_PREPARE. The options are:
SQL_UP_OFF
Temporary stored procedures are not generated for SQLPrepare.
SQL_UP_ON
Temporary stored procedures are generated for SQLPrepare and are not dropped until the connection is closed. This is the default setting.
SQL_UP_ON_DROP
Temporary stored procedures are generated for SQLPrepare. The procedures are dropped the next time SQLPrepare is called on the statement handle, when SQLFreeHandle is called to drop the statement handle, or when the connection is closed.
When SQL_UP_ON is set, most applications realize a performance boost because the SQL Server ODBC driver does not have to continually drop the temporary stored procedures. If an application reprepares an SQL statement when SQL_UP_ON_DROP is set, the driver can reuse the stored procedure created the first time the SQL statement was prepared. Applications that never disconnect (such as a 24x7 application) or that make heavy use of SQLPrepare can see a buildup of #odbc# procedures in tempdb. These applications should set SQL_UP_ON_DROP to alleviate the buildup.
Some APIs that map over ODBC (such as DAO) and the OLE DB Provider for ODBC do not expose the ability to set driver-specific connection attributes. Applications using these APIs cannot dynamically control the SQL_USE_PROC_FOR_PREPARE settings. If these applications use a SQL Server data source, these options can be set on the data source. This is done with the driver-specific UseProcForPrepare keyword on SQLConfigDataSource, or with the procedure options displayed in the SQL Server DSN Configuration Wizard.
To use a statement