Prepared Statements vs Stored Procedures
-
I'm working with an application that relies almost exclusively on prepared statements. When I bring up stored procedures, I'm told that the prepared statements are just as fast so why should we change.
My question is, if I assume that I have 1000 prepared statements active in the application with 100 concurrent users, is the database carrying 1000 prepared statements (shared between users) or 100,000 prepared statements? Are there any other pros/cons of prepared statements vs stored procedures? The database is SQL Server 2000.
-
Hi, do you mean with prepared statements sql embedded in the application code?
If so then one of the advantages of stored procedures is that maintenace will be easier if tables needs to be changed then only the stored procedure needs to be adjusted instead of several strings in the application.
Also security you can give the application only execute rights on the stored procedures and it can not select from the underlying tables.
Also a stored procedure can be tuned and optimized.
-
By prepared statements I mean that the SQL is embedded in the application and SQLPrepare is used to prepare the statement. Then SQLExecute is used with the statement handle to execute the statement.
-
Here read this from SQL BOL
quote:
Prepared ExecutionThe 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
ODBC
How to use a statement (ODBC)
To use a statement
Call SQLAllocHandle with a HandleType of SQL_HANDLE_STMT to allocate a statement handle.
Optionally, call SQLSetStmtAttr to set statement options or SQLGetStmtAttr to get statement attributes.
To use server cursors, you must set cursor attributes to values other than their defaults.
Optionally, if the statement will be executed several times, prepare the statement for execution with SQLPrepare.
Optionally, if the statement has bound parameter markers, bind the parameter markers to program variables by using SQLBindParameter. If the statement was prepared, you can call SQLNumParams and SQLDescribeParam to find the number and characteristics of the parameters.
Execute a statement directly by using SQLExecDirect.
Or
If the statement was prepared, execute it multiple times by using SQLExecute.
Or
Call a catalog function, which returns results.
Process the results by binding the result set columns to program variables, by moving data from the result set columns to program variables by using SQLGetData, or a combination of the two methods.
Fetch through the result set of a statement one row at a time.
Or
Fetch through the result set several rows at a time by using a block cursor.
Or
Call SQLRowCount to determine the number of rows affected by an INSERT, UPDATE, or DELETE statement.
If the SQL statement can have multiple result sets, call SQLMoreResults at the end of each result set to see if there are additional result sets to process.
After results are processed, the following actions may be necessary to make the statement handle available to execute a new statement:
If you did not call SQLMoreResults until it returned SQL_NO_DATA, call SQLCloseCursor to close the cursor.
If you bound parameter markers to program variables, call SQLFreeStmt with Option set to SQL_RESET_PARAMS to free the bound parameters.
If you bound result set columns to program variables, call SQLFreeStmt with Option set to SQL_UNBIND to free the bound columns.
To reuse the statement handle, go to Step 2.
Call SQLFreeHandle with a HandleType of SQL_HANDLE_STMT to free the statement handle.
See Also
Allocating a Statement Handle
Constructing an SQL Statement
Direct Execution
Freeing a Statement Handle
Prepared Execution
SQLBindParameter
SQLDescribeParam
SQLFreeHandle
SQLGetData
SQLGetStmtAttr
SQLMoreResults
SQLRowCount
SQLSetStmtAttr
To prepare and execute a statement
ODBC
How to prepare and execute a statement (ODBC)
To prepare a statement once, and then execute it multiple times
Call SQLPrepare to prepare the statement.
Optionally, call SQLNumParams to determine the number of parameters in the prepared statement.
Optionally, for each parameter in the prepared statement:
Call SQLDescribeParam to get parameter information.
Bind each parameter to a program variable by using SQLBindParam. Set up any data-at-execution parameters.
For each execution of a prepared statement:
If the statement has parameter markers, put the data values into the bound parameter buffer.
Call SQLExecute to execute the prepared statement.
If data-at-execution input parameters are used, SQLExecute returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.
To prepare a statement with column-wise parameter binding
Call SQLSetStmtAttr to set the following attributes:
Set SQL_ATTR_PARAMSET_SIZE to the number of sets (S) of parameters.
Set SQL_ATTR_PARAM_BIND_TYPE to SQL_PARAMETER_BIND_BY_COLUMN.
Set the SQL_ATTR_PARAMS_PROCESSED_PTR attribute to point to a SQLUINTEGER variable to hold the number of parameters processed.
Set SQL_ATTR_PARAMS_STATUS_PTR to point to an array
of SQLUSSMALLINT variables to hold parameter status indicators.Call SQLPrepare to prepare the statement.
Optionally, call SQLNumParams to determine the number of parameters in the prepared statement.
Optionally, for each parameter in the prepared statement, call SQLDescribeParam to get parameter information.
For each parameter marker:
Allocate an array of S parameter buffers to store data values.
Allocate an array of S parameter buffers to store data lengths.
Call SQLBindParameter to bind the parameter data value and data length arrays to the statement parameter.
If the parameter is a data-at-execution text or image parameter, set it up.
If any data-at-execution parameters are used, set them up.
For each execution of a prepared statement:
Put the S data values and S data lengths into the bound parameter arrays.
Call SQLExecute to execute the prepared statement.
If data-at-execution input parameters are used, SQLExecute returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.
To prepare a statement with row-wise bound parameters
Allocate an array
of structures, where S is the number of sets of parameters. The structure has one element for each parameter, and each element has two parts:The first part is a variable of the appropriate data type to hold the parameter data.
The second part is a SQLINTEGER variable to hold the status indicator.
Call SQLSetStmtAttr to set the following attributes:
Set SQL_ATTR_PARAMSET_SIZE to the number of sets (S) of parameters.
Set SQL_ATTR_PARAM_BIND_TYPE to the size of the structure allocated in Step 1.
Set the SQL_ATTR_PARAMS_PROCESSED_PTR attribute to point to a SQLUINTEGER variable to hold the number of parameters processed.
Set SQL_ATTR_PARAMS_STATUS_PTR to point to an array
of SQLUSSMALLINT variables to hold parameter status indicators.Call SQLPrepare to prepare the statement.
For each parameter marker, call SQLBindParameter to point the parameter data value and data length pointer to their variables in the first element of the array of structures allocated in Step 1. If the parameter is a data-at-execution parameter, set it up.
For each execution of a prepared statement:
Fill the bound parameter buffer array with data values.
Call SQLExecute to execute the prepared statement. The driver efficiently executes the SQL statement S times, once for each set of parameters.
If data-at-execution input parameters are used, SQLExecute returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.
Examples
This example shows executing a SELECT statement by using SQLPrepare and SQLExecute. It has been simplified by removing all error checking.
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#define MAXBUFLEN 255
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
int main()
{
RETCODE retcode;
// SQLBindCol variables
SQLCHAR szName[MAXNAME+1];
SQLINTEGER cbName;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
// Allocate an ODBC connection and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
retcode = SQLConnect(hdbc1,
"MyDSN", SQL_NTS, "sa", SQL_NTS,
"MyPassWord", SQL_NTS);
// Allocate a statement handle.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
// Prepare and execute an SQL statement on the statement handle.
// Uses a default result set because no cursor attributes are set.
retcode = SQLPrepare(hstmt1,
"SELECT au_lname from authors", SQL_NTS);
retcode = SQLExecute(hstmt1);
// Simplified result set processing. Bind one column and
// then fetch until SQL_NO_DATA.
retcode = SQLBindCol(hstmt1, 1, SQL_C_CHAR,
szName, MAXNAME, &cbName);
while ( ( retcode = SQLFetch(hstmt1) ) != SQL_NO_DATA )
printf("Name = %s\n", szName);
/* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(0);
}
See Also
Binding Parameters
SQLBindParameter
SQLDescribeParam
SQLPrepare
SQLPutData
SQLSetStmtAttr
Prepared Execution
Using Statement Parameters
See Also
SQLBindParameter
SQLConfigDataSource
SQLFreeHandle
SQLPrepare
SQLSetConnectAttr
This should help. With the advent of SQL 7 and further with 2000 they increased the performance.
-
So in summary if your app is doing something like
sp_prepare "some sql"
execute
execute
execute
execute
sp_prepare "another sql"
execute
execute
then it will be faster but if you app. only fires the execute once for each prepare then you will be better of using stored procedures.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
One of my previous employers also used the SQLPrepare method. Gives a huge performance boost in comparison to direct execution.
However, as is described in the (very long
) post from BOL, it does mean that every user creates his own 'instance' of the same statement. Another drawback is of course the bookkeeping involved (you have to drop the handles before closing the connection).One important reason I see to use SQLPrepare is if you're developping for different DB engines that might not support stored procedure or have different SP syntax (e.g. SQLServer vs. Oracle), then you will have to put in twice (well, 1.5 times) the development effort.
Other reasons could be
-you don't know anything about programming stored procedures (But then I suggest you learn it
. Well worth the effort)- you don't have rights on the database to create procedures (e.g. third party or hosted database)
My conclusion :
If you have a dedicated DB platform and you have complete control over the database, it's better to use SPs.
If you cannot use SPs, try to use the SQLPrepare whenever a statement gets called a lot. It will boost performance significantly.
) post from BOL, it does mean that every user creates his own 'instance' of the same statement. Another drawback is of course the bookkeeping involved (you have to drop the handles before closing the connection).
. Well worth the effort)Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply