INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Optimization

How can I speed up the process of reading from a database? by Ovatvvon
Posted: 1 Feb 02

Database connections can be very costly in time management while working on the web. Therefore, you want to minimize the time connected to the database as much as possible. One nice little feature in ASP is "Disconnected Recordsets". This is the process of retrieving the content you desire into a recordset object, disconnecting from the database to free up the server, then processing the page using the recodset object which still contains the data but is not connected to the database, and then closing the recordset object. Here's how you would do it:


<% @ Language=VBScript %>
<!-- #include file="#ADOVBS.INC" -->
<%

  Dim conn, connString, rs, sql

  sql = "SELECT * FROM myTable;"

  Set conn = Server.CreateObject("ADODB.Connection")
  connString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\InetPub\wwwroot\db\myDatabase.mdb;"
  conn.Open connString

  Set rs = Server.CreateObject("ADODB.Recordset")
  rs.CursorLocation = adUseClient

      'Important, the cursor must reside on the client-side because a server-side cursor cannot be used when the connection is removed from the recordset.


  rs.Open sql, conn, 3, 1, adCmdText

      'adCmdText tells the server that the variable "sql" in the rs.Open line is a textual command line and not a table name (like you would use in an open database connection). You do not need to include this little bit (adCmdText), however it speeds up your connection process just a bit.

  rs.ActiveConnection = Nothing

      'This removes the reference to the connection object from the recordset.

  conn.Close
  Set conn = Nothing

      'There is no longer a connection to the database, however the object "rs" still holds the entire recordset that you can read from until you close it.

  Do while not rs.EOF
    Response.write("Name: " & rs("userName") & "<BR>")
    Response.write("Email: " & rs("userEmail") & "<BR>")
    Response.write("State: " & rs("userState") & "<BR>")
    Response.write("<BR>")
    rs.MoveNext
  Loop

      'Say this Do-While loop would iterate through 2,000 records. This would save a lot of processing power and time rather than if you had been connected to the database while iterating through all of those records!

  rs.Close
  Set rs = Nothing
%>



Hope this helps!

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close