Avoiding Multiple Object Reference

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.