Preparing Commands

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.

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:

• The consumer uses ICommandText::SetCommandText to indicate a new command.
  
  
• The consumer uses ICommandPrepare::Unprepare to indicate that it no longer requires the command text
  
  
• The consumer releases all references to the command object using the temporary stored procedure

A command object will have at most one temporary stored procedure in tempdb. Any existing temporary stored procedure will represent the current command text of a specific command object.