Use a single ADO connection to reduce ASP server load

[neta]

If you need to execute multiple SQL statements on a single database in a single ASP page, it's often wise to open a single Connection and reuse it multiple times. Doing so reduces the number of times the server must open and close the same database, which can significantly increase the server's load. To use a single connection, create the database Connection at the beginning of the page, then destroy it at the end. When you Execute a SQL statement, whether it returns a Recordset or not, specify the active Connection. Here's an example:

<%
strDSN = "DSN=database;UID=username;PWD=password" 
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strDSN 
strSQL = "select column from table" 
Set rsResults=conn.Execute(strSQL, , 1) 
if not rsResults.eof then
temp = rsResults("column")
else
temp = "No Results"
end if 
'close the Recordset, but leave the Connection open
rsResults.Close 
strSQL = "delete from table where column=123"
conn.Execute strSQL, , 1 
'Done executing SQL statements at this point, so close the Connection.
conn.Close set rsResults = Nothing
set conn = Nothing
%>