What is the most effective way for performance to reference a domain value in a recordset?
So far, I have used names to refer to the values of the fields in the recordset. This can be a very inefficient approach because each call requires a lookup domain. To prove this, the following test will refer to the domain (ado__08.asp) by a pointer to the collection of fields in the Recordset:
' Write Data
Do as Not objrs.eof
Response.Write (_
"< TR >" & _
"< TD >" & objRS (0) & "</td >" & _
"< TD >" & objRS (1) & "</td >" & _
"< TD >" & objRS (2) & "</td >" & _
"< TD >" & objRS (3) & "</td >" & _
"< TD >" & objRS (4) & "</td >" & _
"< TD >" & objRS (5) & "</td >" & _
"< TD >" & objRS (6) & "</td >" & _
"</tr >" _
)
Objrs.movenext
Loop
As we expected, the load time changes are small (the difference may be due to a slight reduction in code). But this technique has a significant reduction in the time available for effective display.
In the following example, we will specify a separate variable for each field. This method avoids all lookups within the table loop (ado__09.asp):
If objrs.eof Then
Response.Write ("No Records Found")
Else
' Write headings
...
Dim fld0
Dim Fld1
Dim Fld2
Dim fld3
Dim Fld4
Dim Fld5
Dim Fld6
Set fld0 = objRS (0)
Set fld1 = objRS (1)
Set fld2 = objRS (2)
Set fld3 = objRS (3)
Set fld4 = objRS (4)
Set fld5 = objRS (5)
Set Fld6 = objRS (6)
' Write Data
Do as Not objrs.eof
Response.Write (_
"< TR >" & _
"< TD >" & fld0 & "</td >" & _
"< TD >" & fld1 & "</td >" & _
"< TD >" & fld2 & "</td >" & _
"< TD >" & fld3 & "</td >" & _
"< TD >" & fld4 & "</td >" & _
"< TD >" & fld5 & "</td >" & _
"< TD >" & fld6 & "</td >" & _
"</tr >" _
)
Objrs.movenext
Loop
Set fld0 = Nothing
Set fld1 = Nothing
Set fld2 = Nothing
Set fld3 = Nothing
Set Fld4 = Nothing
Set fld5 = Nothing
Set Fld6 = Nothing
Response.Write ("</table >")
End If
Until now, the results of this method are the best. The display time for each record dropped to. 45 milliseconds.
The configuration of all test scripts now requires some understanding of the result recordset. For example, we always encode the domain name in the column header, referencing the values of these fields separately. The following example provides a dynamic solution that loops through the set of domains, not only to get the data, but also to get the title of the domain (ado__10.asp):
If objrs.eof Then
Response.Write ("No Records Found")
Else
' Write headings
Response.Write ("< TABLE border=1 >< TR >")
For each objfld in Objrs.fields
Response.Write ("< TH >" & objfld.name & "</th >")
Next
Response.Write ("</tr >")
' Write Data
Do as Not objrs.eof
Response.Write ("< TR >")
For each objfld in Objrs.fields
Response.Write ("< TD >" & Objfld.value & "</td >")
Next
Response.Write ("</tr >")
Objrs.movenext
Loop
Response.Write ("</table >")
End If
As you can see, we have a loss on performance, but this method is still faster than ado__07.asp.
The following test is a compromise between the last two Tests. By saving a reference to a domain in a dynamically allocated array, both dynamic flexibility and some loss of performance are saved.
If objrs.eof Then
Response.Write ("No Records Found")
Else
Dim Fldcount
Fldcount = ObjRS.Fields.Count
Dim FLD ()
ReDim FLD (Fldcount)
Dim I
For i = 0 to FldCount-1
Set FLD (i) = objRS (i)
Next
' Write headings
Response.Write ("< TABLE border=1 >< TR >")
For i = 0 to FldCount-1
Response.Write ("< TH >" & FLD (i). Name & "</th >")
Next
Response.Write ("</tr >")
' Write Data
Do as Not objrs.eof
Response.Write ("< TR >")
For i = 0 to FldCount-1
Response.Write ("< TD >" & FLD (i) & "</td >")
Next
Response.Write ("</tr >")
Objrs.movenext
Loop
For i = 0 to FldCount-1
Set FLD (i) = Nothing
Next
Response.Write ("</T
