Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unloading Objects From Memory: Performance Issue 2

Status
Not open for further replies.

gwinn7

Programmer
Feb 10, 2001
1,004
US
Recently, I ran a test to see how long it would take to load about 100,000 records into a collection from DAO in Access. The process was adequate (less than 10 seconds).

Each record was loaded in to an object I defined from a Class; then stacked into a collection as the records were read in.

Everything worked great until I tried to quit the application. Access was extremely slow in freeing the memory it had eaten from creating the objects in the collection. It took about 15-20minutes for the app to free all its memory and shutdown.

Does anyone know of a faster way of freeing this memory besides setting the collection to "Nothing"? Some neat Windows API technique or something?

Any assistance is appreciated. :)

Gary
gwinn7
 
How about using the Remove method to delete each item in the collection first? If adding the items was quick, maybe removing them would be, too.

I'd try it with a For loop from Collection.Count - 1 To 0 Step -1. Assuming the collection uses an array structure internally, it might be more efficient if it didn't have to keep shifting all the array entries down as you removed items.

Let us know if that works out faster. Rick Sprague
 
Good suggestion. I will try that when I am at that PC tomorrow and let you know.

However, one additional question...

I am under the impression that the For Each loop structure is generally faster than the For Loop structure when it comes to Collections. Is there a specific reason for suggesting the For Loop over the For Each? I didn't quite get that in your response.

Thanks,
Gary
gwinn7
 
Yes, there is. For Each will always iterate from first item to last. As I said, it may be faster to remove them in reverse order, so the collection doesn't have to shift items downward in its array. Normally it wouldn't make a lot of difference, but when you have 100,000 items, that's a LOT of shifting - 50 million individual shifts, if I'm not mistaken. That could, in fact, be why setting the collection to Nothing is so slow--internally, VBA may be doing a For Each to remove the items. Rick Sprague
 
Ahhh! I see! Thank you for clarifying that!

Gary
gwinn7

 
Back again,

The results of the test are about the same. I tried the For Loop and compared it with the For Each. Both seem to work nearly at the same speed.

Back to the drawing board. I did notice in a Windows API book that I can allocate and free memory resources, but I am not sure how to apply it here.

Any other ideas?

Gary
gwinn7
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top