Improper coding techniques with unnecessary object references can also affect
performance. During Dynaset object navigation, you should reduce the number of
object references to OraField collection and OraField object. The following is
an inefficient code block:
'Create the OraDynaset Object
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
'Traverse until EOF is reached
Do Until OraDynaset.EOF
msgbox OraDynaset.Fields("sal").value
OraDynaset.MoveNext
Loop
In the previous example, OraDynaset, OraField collections, and OraField object
are referenced for each iteration. Although OO4O provides improvement in
handling the field collections object, multiple references to the automation object
goes though underlying OLE/COM automation layer, which in turn slows down the
execution. The following example shows how to reference fields through a field
object and not through the fields collection of the dynaset. Testing has
determined that this small amount of extra code greatly improves performance.
Dim flds() As OraField
Dim i, fldcount As Integer
' Create the OraDynaset Object
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
' Get the field count, and output the names
fldcount = OraDynaset.Fields.Count
ReDim flds(0 To fldcount - 1)
For i = 0 To fldcount - 1
Set flds(i) = OraDynaset.Fields(i)
Next I
'Traverse until EOF is reached
Do Until OraDynaset.EOF
msgbox Flds(5).Value
msgbox Flds(6).Value
OraDynaset.MoveNext
Loop
Any method or object that is referenced through more than one object is
potentially inefficient, but the extra coding is not always worth the time saved, as
in the first example of this section. The best place to start is with field
references, because they are most likely to occur multiple times.
