Executing Prepared Statements
Prepared execution is commonly used by applications to execute the same parameterized SQL statement repeatedly. Prepared execution is faster than direct execution for statements executed more than three or four times 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. The Prepared property of the Command object allows you to specify whether to prepare a statement.
An ADO application can use prepared execution to reduce the parsing and compiling overhead associated with repeatedly executing an SQL statement that is executed numerous times. The application builds a character string containing an SQL statement and then uses the Prepared property to have the provider save a prepared (or compiled) version of the query specified in the CommandText property before the first execution of a Command object. This can slow the first call of the Execute method, but after the command is compiled, the provider uses the compiled version of the command for any subsequent executions, which results in improved performance.
If the Prepared property is set to False, the provider executes the Command object directly without creating a compiled version.
The Prepared property can be used when executing a statement with multiple parameter sets. An application can execute a parameterized statement more than once by supplying a different parameter set at each execution instead of reconstructing the statement whenever the parameter set is different.
Microsoft® SQL Server™ 2000 continues to support the prepare/execute model of OLE DB and ODBC. For applications using the Microsoft OLE DB Provider for ODBC (MSDASQL), this option can be disabled through the SQL Server ODBC Data Source Setup dialog box if an ODBC data source is used to connect to an instance of SQL Server. If the option is disabled, the SQL statement is stored and then sent to the server each time it is executed.
This example shows using a prepared statement to update a query and construct the query dynamically with a different set of parameters at execution time.
Dim cn As New ADODB.Connection
Dim cmdPrep1 As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Dim strCn As String
strCn = "Server=MyServerName;Database=pubs;Trusted_Connection=yes"
cn.Provider = "sqloledb"
cn.Open strCn
Set cmdPrep1.ActiveConnection = cn
cmdPrep1.CommandText = "UPDATE titles SET type=? WHERE title_id =?"
cmdPrep1.CommandType = adCmdText
cmdPrep1.Prepared = True
Set prm1 = cmdPrep1.CreateParameter("Type", adChar, adParamInput, 12, "New Bus")
cmdPrep1.Parameters.Append prm1
Set prm2 = cmdPrep1.CreateParameter("ProductID", adInteger, adParamInput, 4, 3)
cmdPrep1.Parameters.Append prm2
cmdPrep1.Execute
cmdPrep1("Type") = "New Cook"
cmdPrep1("title_id") = "TC7777"
cmdPrep1.Execute
cn.Close
Data is updated in the titles table by using different parameter values. The query string is prepared so that different sets of parameters can be supplied. Two parameters are required for the update operation: type and title_id. They are created by the two CreateParameter methods and appended to the Parameters collection with the Append method.
The first set of parameters has the values New Bus and BU7832. Because the Prepared property is set to TRUE, different values can be supplied to cmdPrep1 without reconstructing and re-executing the query string.
Note Prepared statements cannot be used to create temporary objects on SQL Server. Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. An application must directly execute these procedures.