INF: SQLPrepare and Temporary Stored Procedures in SQL Server

• Microsoft SQL Server, versions 6.0 & 6.5
• Microsoft Open Database Connectivity

SUMMARY

This article explains the circumstances under which a temporary stored procedure is created when the autocommit mode is turned off and SQLPrepare is called.

MORE INFORMATION

SQLPrepare is used to obtain better performance when the same SQL statement, with or without the same data, is executed repeatedly. In Microsoft SQL Server 6.5, it is implemented by creating a temporary stored procedure.

The Microsoft SQL Server driver can be configured to generate stored procedures to support the ODBC SQLPrepare statement. SQL Server version 6.0 introduced temporary stored procedures, which are now used by the ODBC driver when it creates its procedures for prepared statements. For more information on how these procedures use the TEMPDB in SQL Server and when the server cleans them up, refer to the following Microsoft Knowledge Base article:

INF: SQL Server 6.0 ODBC Driver Changes Tempdb Usage ID: Q135532

In the following discussion, it is assumed that you are using the SQLSetConnectOption API to set the SQL_USE_PROCEDURE_FOR_PREPARE option to SQL_UP_ON or SQL_UP_ON_DROP. When you use the ODBC Administrator to configure a SQL Server data source, the check box 'Generate Stored Procedure for Prepared Statement' represents SQL_UP_ON or SQL_UP_OFF depending on whether it is selected or cleared respectively. In order for the driver to generate a stored procedure, leave this option selected (default).

Consider a scenario where there are two or more statement handles on a SQL Server 6.5 connection and autocommit mode is turned off on that connection. Assume that you want to use the above statement handles to prepare different SQL statements. Only the first SQLPrepare in the sequence will generate a temporary stored procedure when there is no user-defined transaction pending. Subsequent SQLPrepare calls using the other statement handles will not generate temporary stored procedures.

For example:

/**************************************************************************

In ISQL/W:

CREATE TABLE test(Col1 char (10) NULL ,Col2 char (10) NULL)

Insert into test values ('Hello','Subba')

ODBC C program:

HENV henv; HDBC hdbc; HSTMT hstmt1,hstmt2;

UCHAR Dsn[ ] = "MySQLServer"; UCHAR Uid[ ] = "sa"; UCHAR Pwd[] = "yourpwd"; UCHAR *szCol1="Hello", *szCol2 = "Subba";

SWORD cbDSN = 11, cbUid = 2; cbPwd = 7;   //these are lenghts of datasource

SQLAllocEnv(&henv); SQLAllocConnect(henv, &hdbc); SQLSetConnectOption(hdbc, SQL_USE_PROCEDURE_FOR_PREPARE ,SQL_UP_ON_DROP) SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF); SQLConnect(hdbc, Dsn, cbDSN, Uid, cbUid, Pwd, cbPwd);

SQLAllocStmt(hdbc, &hstmt1); SQLAllocStmt(hdbc, &hstmt2);

SQLPrepare(hstmt1,(UCHAR *)"select * from test where Col1 = ?", SQL_NTS); SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, szCol1, sizeof(szCol1), &pcbVal1);

SQLExecute(hstmt1);        /* this will create a temporary stored

SQLPrepare(hstmt2,(UCHAR *)"select * from test where Col2 = ?", SQL_NTS); SQLBindParameter(hstmt2, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, szCol2, sizeof(szCol2), &pcbVal2);

SQLExecute(hstmt2);     /* Does not create temporary stored procedure*/

/**************************************************************************

To create a stored procedure, temporary or permanent, SQL Server obtains locks on some of the system tables.

The SQLExecute on hstmt1 does create the temporary stored procedure because there is no transaction active at that time. On the SQL Server, the implicit transaction started from creating the above procedure is immediately committed to prevent locks on system tables.

The second SQLExecute (hstmt2), however, does not create a temporary stored procedure because there is already a user defined transaction active (from the first SQLExecute). Creating a procedure for the second SQLExecute at this time would cause locks on system tables which would be held until the user commits or rolls back the transaction. This is bound to be a potential performance problem, so not creating a procedure for the second SQLExecute is by design.

Going to a 6.0 SQL Server is similar except that the create procedure can not appear in an active transaction, so only the 1st execution can create the procedure.

The implication of the above behavior is that calling SQLExecute on hstmt1 repeatedly would give you a better performance because of the temporary stored procedure created. On the other hand, SQLExecute on hstmt2 would be identical to SQLExecDirect as far as performance is concerned due to the lack of a stored procedure. Applications can, however, workaround this behavior by keeping transactions short. For example, the following modifications to the above code would facilitate the generation of a stored procedure for the SQLExecute on hstmt2. For simplicity, the variable declaration is omitted here.

/**************************************************************************

SQLAllocStmt(hdbc, &hstmt1); SQLAllocStmt(hdbc, &hstmt2);

SQLPrepare(hstmt1,(UCHAR *)"select * from test where Col1 = ?", SQL_NTS); SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, szCol1, sizeof(szCol1), &pcbVal1);

SQLExecute(hstmt1);              /* this will create a temporary stored

SQLTransact(henv, hdbc, SQL_COMMIT);   /*commit the first transaction */

SQLExecute(hstmt2);           /* creates a temporary stored procedure*/
SQLTransact(henv, hdbc, SQL_COMMIT);   /*commit the second transaction */
/**************************************************************************

With the above modification, you can call both SQLExecutes repeatedly and gain good performance. Note that the transaction commit behavior of this second sample is different from in the first sample. Also, if you can leave the autocommit mode on, which is the default connection option, temporary stored procedures will be created for all the prepared statements.