ASP-Database Best Practices
Retrieving data from a Microsoft SQL Server using ADO (ActiveX Data Objects) and ASP can be challenging to Web developers. As an increasing number of Web applications are serving as the interface to databases, it is important that you understand ways to maximize performance, scalability, and robustness in your development efforts. Use the following guidelines and best practices if you plan on calling database components from Web applications in IIS:
With ADO, always close recordsets and connections.
If you "open" a connection, use it and "close" it again. The connection can then safely be handed to another thread processing a different command. If the server load gets light, the connection pool is trimmed back automatically and others using that server get better performance. If the server load gets heavy, the pool can grow as needed. Choosing not to pool connections will result in idle connections, which waste server and network resources. In addition, you also may discover threading issues that can occur if multiple concurrent threads end up using the same connection.
Q176056: ADO/ASP Scalability FAQ
Open late-close early.
Open ADO objects just before they're needed and close right after you're done. This shortens the time span in which the database must juggle resources for you, and releases the database connection to the connection pool as quickly as possible to allow new connections.
25+ ASP Tips to Improve Performance and Style
Do not pass parameters to the command object in the execute statement. Passing parameters to the command object forces ADO to handle extra processing duties as well as makes assumptions about the parameters you pass in.
The following code sample illustrates the poor practice of passing parameters to the command object.
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Open "Provider=SQLOLEDB;Data
Source=dsnTest;Database=dbTest;UserId=john;Password=doe;"
Set RS = DB.Execute ("GetCustomerByLastName @LastName='Smith'")
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
A better practice is to explicitly declare parameters for the command object as displayed in the code sample below.
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Open "Provider=SQLOLEDB;Data
Source=dsnTest;Database=dbTest;UserId=john;Password=doe;"
Set cmdTemp = Server.CreateObject ("ADODB.Command") cmdTemp.ActiveConnection = DB
cmdTemp.CommandText = "GetCustomerByLastName"
cmdTemp.CommandType = adCmdStoredProc
Set params = cmdTemp.Parameters
params.Append cmdTemp.CreateParameter ("RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdTemp.CreateParameter ("@LastName", adVarChar, adParamInput,
15)
cmdTemp ("@LastName") = "Smith"
Set RS = cmdTemp.Execute
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
Set cmdTemp = Nothing
Always use Server.CreateObject.
Using Server.CreateObject allows ASP to track the object instance. The server portion causes the object to be created in a transaction server package so resources are pooled. Using the CreateObject and GetObject functions in server-side scripts rather than Server.CreateObject does not allow for access to ASP built-in objects or participate in transactions. Using CreateObject and GetObject will attach each new object to a separate thread which will consume available system resources much faster than using the connection pooling features available by using Server.CreateObject.
ASP Component Guidelines
Do not re-use recordset or command variables; create new ones.
Re-using recordset or command variables may increase the risk of the code causing a failure within ADO. The Command Object is not designed or intended for this kind of utilization.
Q197449: Problems Reusing ADO Command Object on Multiple Recordsets
While configuring ODBC settings for your data source, use System DSN's as much as possible(rather than File DSN's). A System DSN is 3 times faster than a File DSN.
Don't put ADO connections in session objects.
When ADO objects are put in sessions, scalability limitations and threading issues are introduced, as well as unnecessary high stress on both the Web server and the database. If a connection is stored in a Session variable, connection pooling is eliminated because variables stored in the Sessionobject persist for the entire user-session. Connection pooling is profitable when connections are shared across multiple clients and resources are in use only as long as they are needed. A Connection object stored in a Session variable will only serve the user for which the Session was created, and the Connection will not be released to the pool until the end of the Session.
Top Ten Tips:Accessing SQL Through ADO and ASP
MSDN Library: Session Object
Use TCP/IP sockets to connect to SQL Server if it is running on a remote computer.
TCP/IP sockets do not require an NT trusted connection and use standard SQL security, bypassing the authentication issues that are associated with using Named Pipes to a remote computer (see below). In cases where the SQL server is on another computer, TCP/IP sockets will offer a faster connection.
Top Ten Tips:Accessing SQL Through ADO and ASP
Use Named Pipes if SQL Server is running locally (on the same machine as ASP.)
By default, the use of network named pipes requires a trusted connection. When a client attempts a connection over network name pipes, the SQL Server Windows NT box performs a security check and must authenticate the client’s computer account which requires a round trip to the appropriate domain controller. If the path between the SQL Server and the domain controller is unavailable, a connection may not be established.
In the case where SQL Server is running on the same machine running IIS and hosting ASP, use a local named pipe connection instead of a network named pipe connection. A simple way to do this is to change the keyword SERVER=machinename to SERVER=<local> in the SQL Server connection string of the global.asa file. This will prevent the round trip to a domain controller for authentication saving precious network bandwidth.
Q207671: How to: Accessing Network Files from IIS Application
Ensure that connection objects are created in each asp page.
Creating and destroying the Connection object on a per-page basis rather than storing it in a Session variable takes advantage of connection pooling. Destroying the object on every page releases the connection to the pool so that it may be reused. This resource sharing reduces the load on the server and can reduce database connection time for users after the initial connections are created. In a complex ISAPI scenario, the Microsoft Performance Group has measured between a 30% - 40% CPU utilization improvement by using an optimized connection pooling implementation. This measurement was taken using performance benchmarks such as TPC-C and TPC-W.
Top Ten Tips:Accessing SQL Through ADO and ASP