Why is there a difference in speed?
Consider this code:
function Foo(ds as OtherDLL.BaseObj)
dim lngRowIndex as long
dim lngColIndex as long
for lngRowIndex = 1 to ubound(ds.Data, 2)
for lngColIndex = 1 to ds.Columns.Count
Debug.Print ds.Data(lngRowIndex, lngColIndex)
next
next
end function
OK, a little context. The parameter ds
is of type OtherDLL.BaseObj, which is defined in the corresponding ActiveX DLL. ds.Data
is a variant of a 2-dimensional array (one dimension carries data, the other carries a column index. ds.Columns
is a collection of columns in 'ds.Data`.
Assuming there are at least 400 rows of data and 25 columns, this code takes about 15 seconds to run on my computer. The view is incredible.
However, if I copy the options array into a local variable like so:
function Foo(ds as OtherDLL.BaseObj)
dim lngRowIndex as long
dim lngColIndex as long
dim v as variant
v = ds.Data
for lngRowIndex = 1 to ubound(v, 2)
for lngColIndex = 1 to ds.Columns.Count
Debug.Print v(lngRowIndex, lngColIndex)
next
next
end function
the whole thing is processed at a barely noticeable time (mostly close to 0).
Why?
a source to share
Most likely, ds.Data creates / copies the entire array every time it is accessed (for example, retrieving it from the database or something like that) and this takes a significant amount of time.
Once you access it inside the loop, you do this copy operation hundreds of times. In the second case, you copy it once to a local variable outside of the loop, and then quickly reuse that data hundreds of times without copying it over again.
This is a common and very important optimization technique: move any unnecessary work out of the loop so that it runs as little as possible. It's just that in your example it isn't "obvious" that it does a lot of extra work when accessing Data.
a source to share