[thelist] Friday Freebie

Scott Dexter sgd at ti3.com
Fri Nov 3 11:15:41 CST 2000

<tip type="little perf kicks" author="Scott Dexter"
email="sgd at thinksafely.org">
Let's say we want to pull some information out of a database table. I know,
old hat, right?

We can squeeze some performance out by referencing the columns by their
ordinal position rather than their names. If you're mushing through a large
recordset, it amounts to quite a bit. There are some catches, though. Since
we're taking control away from VBScript, we have to be better programmers to
make sure the stuff don't break:

1) Specify the column names in the query, DO NOT use Select * --you can't
guarantee the order of the columns otherwise (and its an easy reference when
you're writing the code)
2) Cast the values when you set them to local variables --when specifying by
column name, ADO looks up the name and data type of the value for you, and
casts it appropriately when you assign it to a variable. Now that we're
taking away that lookup, *we* have to cast it (unless you like "type
mismatch" errors)
3) ADO uses zero-based numbering; the first column is column 0, the second
is column 1, etc

' *** Select 0,1,2,3 from table
sqlstr="Select uid,username,email,createdate from tbUsers order by username"

' *** SetupSQL is a function we wrote, code below
Set oConn=SetupSQL("DSN=UserDB;uid=IISuser;pwd=yomama")

Set oRS = oConn.Execute(sqlstr)
if oRS.BOF and oRS.EOF then
 response.write "<i>No Records found</i>"
 Response.write "<table>"
 While not oRS.EOF
  '*** Casting the right types. look ma, I'm in pictures!!
  uid = CLng(oRS(0))
  email = CStr(oRS(2))

  userrow="<tr><td>"& uid &"</td><td>"& username &"</td><td>"& _
	email &"</td><td>"& createdate &"</td></tr>"
  response.write userrow & vbNewLine

 response.write "</table>"
 ' *** CloseSQL is also something we wrote, code below
 CloseSQL oRS
end if
CloseSQL oConn

Function SetupSQL(byval connstr)
Set DBCON = Server.CreateObject("ADODB.Connection")
DBCON.ConnectionTimeout = 15
DBCON.CommandTimeout = 60
DBCON.Open connstr
Set SetupSQL = DBCON
Set DBCON = Nothing
End Function

Sub CloseSQL(byref DBOBJ)
Set DBOBJ = Nothing
end sub

work: http://www.ti3.com/
non: http://thinksafely.org/

More information about the thelist mailing list