Outlook collection taking 100x longer to delete than create

[bonkey]

Having a weird issue. I am loading in some data from a file it makes about 1.6 million records in a collection. (collection of class objects).

When I am all done and want to close the spreadsheet it takes over 30 minutes to close.

I tested it by setting all my in memory objects to nothing that the issue is in my giant collection.

It only takes a short time to read the file and build the collection list but setting that collection = nothing takes forever (over 30 mins).

Any ideas?

 

[Norie]

Re: Collection taking 100x longer to delete than create

Do you have code that's deleting the collection?

If you do can you post it?

 

[bonkey]

Re: Collection taking 100x longer to delete than create

Good question.

This happens if I :
1) hit the stop button in the debugger
2) set myCollection = Nothing

 

[Norie]

Re: Collection taking 100x longer to delete than create

What happens if you use something like this?

Set myCollection = New Collection

That basically replaces the existing collection myCollection with an 'empty' collection.

 

[bonkey]

Re: Collection taking 100x longer to delete than create

Just tried setting it to new collection. Same issue. (It is still running, but I assume it will be 30 mins).

going to try this next:

dim i as longlong
for i = 1 to myCollection.Count
  set myCollection.Item(i) = Nothing
  myCollection.Remove(i)
Next i

 

[bonkey]

Re: Collection taking 100x longer to delete than create

Still no joy.

Had to tweak the code slightly.

dim i as longlong

for i = myCollection.Count to 1 step -1
  myCollection.Remove i

still didn't help though. I am at a loss.

 

[bonkey]

Re: Collection taking 100x longer to delete than create

Update:

Performed an experiment where I took my Loop sample that deletes quickly and copied it into my code that takes 30 mins to delete. I ran my normal code and loaded it all into memory. When it finished I ran the Loop sample and deleted it. I was able to duplicate the 30 minute hang.

So, I infer from that that the issue is somehow environmental. Not sure what it could be though. My code is not running at this point it is just an idle spreadsheet with lots of stuff in memory.

The search continues.

 

[RoryA]

Re: Collection taking 100x longer to delete than create

Does your class do anything in its Terminate event?

 

[bonkey]

Re: Collection taking 100x longer to delete than create

The plot thickens.

I run my loop code BEFORE running any of my normal code and it still hangs.

I then reduced the number of records by a factor of 10 (I dropped a 0). it still runs slow but finished.

I ran the shorter test a second time and IT CRASHED OUTLOOK. My code has nothing to do with outlook. This is just too weird.

 

[bonkey]

Re: Collection taking 100x longer to delete than create

Okay, so now even my example loop code won't delete. So here it is. You may be able to reproduce the issue if you run it multiple times.

Class thing

Public string1 As String
Public double1 As Double
Public int1 As Integer
Public date1 As Date
Public curr1 As Currency
Public col1 As Collection
Public var1 As Variant
Public arr1 As Variant


Private Sub Class_Initialize()
    Set col1 = New Collection
End Sub

Module 1

Public TotalCollection As Collection


Sub test()
    Dim i As LongLong
    Dim aThing As thing
    
    Set TotalCollection = New Collection
    
    For i = 1 To 1700000
        Set aThing = New thing
        
        aThing.arr1 = Array("stuff", "more stuff", 1, 5, 6, "extra stuff")
        Set aThing.col1 = New Collection
        aThing.col1.Add "stuff"
        aThing.col1.Add "stuff1"
        aThing.col1.Add "stuff2"
        aThing.col1.Add "stuff3"
        aThing.col1.Add "stuff4"
        aThing.curr1 = 1234567.89
        aThing.date1 = Date
        aThing.double1 = 123456789.123457
        aThing.int1 = 42
        aThing.string1 = "THis is a string. Cool huh?"
        aThing.var1 = ";lskjdflkdsjfldsf"
        
        TotalCollection.Add aThing, aThing.string1 & CStr(i)
        
        Set aThing = Nothing
    Next i
    
End Sub


Sub delete()
    Set TotalCollection = Nothing
End Sub