Data Access and Transactions

Prepared Queries

If a SQL statement will be used multiple times, you can potentially improve the performance of your application with prepared queries. When a SQL statement is prepared, a temporary stored procedure is created and compiled. This procedure is executed when the prepared statement is called, which saves the overhead of parsing the command each time it is used.

The following example demonstrates the use of a prepared query:

<%
   Set cmd = Server.CreateObject("ADODB.Command")
   cmd.ActiveConnection = Application("ConnectionString")
   cmd.Prepared = True
   cmd.CommandText = "Select * From Catalogue Where Id=?"
   Set p = cmd.Parameters
   p.Append cmd.CreateParameter("prodId",adInteger,adParamInput)
   cmd("prodId") = Request("Id1")
   Set rs = cmd.Execute
   
   cmd("prodId") = Request("Id2")
   Set rs = cmd.Execute
%>

Usually, prepared queries are dropped when the connection is released. When connection pooling is enabled, however, you risk running out of space in your temporary database if your connections are recycled often enough. If you use prepared queries, you can configure the SQL Server driver to drop queries “as appropriate” when connection pooling is enabled. You can select this option in the Data Sources (ODBC) application (in Administrative Tools) in Control Panel.