How can I speed up the process of reading from a database?

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:

[color blue]
<% @ 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

[color green]'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.[/color]


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

[color green]'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.[/color]

rs.ActiveConnection = Nothing

[color green]'This removes the reference to the connection object from the recordset.[/color]

conn.Close
Set conn = Nothing

[color green]'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.[/color]

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

[color green]'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![/color]

rs.Close
Set rs = Nothing
%>
[/color]


Hope this helps!