When using ADO in ASP, make sure to...

• Always close recordsets and connections.

  rs.close
  set rs=nothing
  conn.close
  set conn=nothing
  					

  For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  176056  (http://kbalertz.com/Feedback.aspx?kbNumber=176056/ ) ADO/ASP scalability FAQ
• Open late, close early: Open ADO objects just before they're needed and close them immediately after you're done. This frees resources while other logic is processing.
• Don't create ADO objects in session variables. This effectively bypasses MTX connection and thread pooling. If threads aren't pooled, each object created per user can tie up a thread. Also, if the object isn't specifically closed, a session object variable can live and tie up a thread for the life of a session (20 minutes after the last click).
• Do not pass parameters to the command object in the execute statement.
• If you are not marshalling data through a firewall, instantiate objects with Server.CreateObject. The Server part tells Microsoft Transaction Server to create the object in a Transaction Server package so that resources are pooled. (Note this does not apply to IIS 5.0/COM+.)
• Do not reuse recordset or command variables. Create new ones. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  197449  (http://kbalertz.com/Feedback.aspx?kbNumber=197449/ ) PRB: Problems reusing ADO Command object on multiple recordsets

Additional items to consider

• While configuring Open Database Connectivity (ODBC) settings for your data source, use system data source names (DSNs) as much as possible, rather than file DSNs. A system DSN is three times faster than a file DSN.
• Don't put ADO connections in session object. ODBC 3.x does connection pooling automatically for you.
• Use TCP/IP sockets to connect to Microsoft SQL Server if it is running on another computer. Use named pipes if SQL Server is running on the same computer as Active Server Pages (ASP).
• Ensure that a proxy account is used to connect to Oracle because Oracle assigns user context to each thread.
• Ensure that connection objects are created in each ASP page.

PerfMon counters

• Errors-related counters (such as "Errors During Script Runtime") - these should be near zero in value. Any errors-related counters that are significantly larger than zero should be further investigated before proceeding with troubleshooting.
• Requests executing - This is very important because it tells you whether your application is forced to be single-threaded or not. The default worker threads for executing ASP requests in IIS 4.0 is 10 (ProcessorThreadMax in the registry). If the ASP Requests Executing counter returns a value of "1", then your requests are definitely being serialized for some reason. Make sure Web Application debugging is not on because this will serialize your requests. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  216580  (http://kbalertz.com/Feedback.aspx?kbNumber=216580/ ) PRB: Blocking/serialization when using InProc component (DLL) from ASP
• Requests queued - If this number keeps increasing continuously, your ASP pages have blocked all threads for some reason and no threads are being released to service additional requests from the queue.
• Sessions total - This is the total number of sessions since the Web service was started. You may want to stop and restart the Web service before a test run to more accurately monitor the total sessions being created for a specific test script run. Make sure that while your script is running this number keeps gradually increasing until it reaches the desired total.

ADO/MDAC

Back to the top

Articles on best practices for developing Data Access-enabled Web applications

Data Access

General ASP

Back to the top