[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
Example:
<%
' *** 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>"
else
Response.write "<table>"
oRS.MoveFirst
While not oRS.EOF
'*** Casting the right types. look ma, I'm in pictures!!
uid = CLng(oRS(0))
username=CStr(oRS(1))
email = CStr(oRS(2))
createdate=CDate(oRS(3))
userrow="<tr><td>"& uid &"</td><td>"& username &"</td><td>"& _
email &"</td><td>"& createdate &"</td></tr>"
response.write userrow & vbNewLine
oRS.MoveNext
Loop
response.write "</table>"
' *** CloseSQL is also something we wrote, code below
CloseSQL oRS
end if
CloseSQL oConn
Function SetupSQL(byval connstr)
Dim DBCON
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)
DBOBJ.Close
Set DBOBJ = Nothing
end sub
%>
</tip>
sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/
More information about the thelist
mailing list