SQLOLEDB supports command preparation for optimized multiple execution of a single command; however, command preparation generates overhead, and a consumer does not need to prepare a command to execute it more than once. In general, a command should be prepared if it will be executed more than three times.
In Microsoft® SQL Server™ version 7.0, when a command is executed directly (without preparing it first), an execution plan is created and cached. If the SQL statement is executed again, SQL Server 7.0 has an efficient algorithm to match the new statement with the existing execution plan in the cache, and reuses the execution plan for that statement.
For prepared commands, SQL Server 7.0 provides native support for preparing and executing command statements. When you prepare a statement, SQL Server creates an execution plan, caches it, and returns a handle to this execution plan to the provider. The provider then uses this handle to execute the statement repeatedly. No stored procedures are created. Because the handle directly identifies the execution plan for an SQL statement instead of matching the statement to the execution plan in the cache (as is the case for direct execution), it is more efficient to prepare a statement than directly execute it, if you know it will be executed more than a few times.
When connected to SQL Server 6.5, SQLOLEDB may create a temporary stored procedure when command text is prepared. Some commands should never be prepared. For example, commands that specify stored procedure execution or include invalid text for SQL Server stored procedure creation should not be prepared.
If a temporary stored procedure is created, SQLOLEDB executes the temporary stored procedure, returning results as if the statement itself was executed.
Temporary stored procedure creation is controlled by the SQLOLEDB-specific initialization property SSPROP_INIT_USEPROCFORPREP. If the property value is either SSPROPVAL_USEPROCFORPREP_ON or SSPROPVAL_USEPROCFORPREP_ON_DROP, SQLOLEDB attempts to create a stored procedure when a command is prepared. Stored procedure creation succeeds if the application user has sufficient SQL Server permissions.
For consumers that infrequently disconnect, creation of temporary stored procedures can require significant resources of tempdb, the SQL Server system database in which temporary objects are created. When the value of SSPROP_INIT_USEPROCFORPREP is SSPROPVAL_USEPROCFORPREP_ ON, temporary stored procedures created by SQLOLEDB are dropped only when the session that created the command loses its connection to the SQL Server. If that connection is the default connection created on data source initialization, then the temporary stored procedure is dropped only when the data source becomes uninitialized.
When the value of SSPROP_INIT_USEPROCFORPREP is SSPROPVAL_USEPROCFORPREP_ON_DROP, SQLOLEDB temporary stored procedures are dropped when one of the following occurs:
A command object has at most one temporary stored procedure in tempdb. Any existing temporary stored procedure represents the current command text of a specific command object.