Using Stored Procedures with ADO

A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Creating stored procedures with connection pooling enabled can present some special considerations. For example, creating temporary stored procedures for prepared SQL statements is an option that is configurable from the ODBC Data Source Administrator. The default for this setting is "On" for the SQL Server 2.65 and 3.5 drivers, which means that when a SQL statement is prepared, a temporary stored procedure is created and compiled. When the prepared command is called by one of the ADO methods, the temporary stored procedure is executed, saving the overhead of parsing and compiling the SQL statement. This feature can improve the performance of your application, if it is used properly. If your SQL statement is going to be executed more than twice, or if it will be run more than once and contain parameters, the statement should be prepared. Keep in mind that to prepare a SQL statement it must be compiled the first time you run it, and that the preparation is lost once you disconnect from the database.

When connection pooling is enabled, you have to decide when temporary stored procedures should be deleted. With the SQL Server 2.65 driver, stored procedures will be released when the connection is released. With the SQL Server 3.5 driver, you have the option of deleting them either when the connection is released, or as appropriate while connected.

There can be storage problems with temporary stored procedures and connection pooling. If you are using the default setting, you risk of running out of space in TempDB, where temporary stored procedures are created and stored. When connection pooling is enabled, a connection is made to the database, but when the client is finished with it and releases it, the connection goes back into the pool. The connection is not released and the stored procedures are not deleted from the TempDB-even though they are no longer associated with a client and will never be called again.

Thus, when running the SQL Server 2.65 driver, it may be advisable to disable the creation of stored procedures on preparation when connection pooling is enabled. With the SQL Server 3.5 driver, the option to create temporary stored procedures should be disabled or set to "disconnect and as appropriate" when running connection pooling. Setting this option to "disconnect and as appropriate" means that the ODBC SQL Server driver will drop the connection when the OLE DB ICommand object that created the temporary stored procedure is released. If your client code uses ADO, the stored procedure would be released when the ADODB.Recordset and ICommand objects are closed.