MS Access - Large Memory Usage/Leakage

[FuSoftware]

This is half an enhancement, half a possible bug (memory leakage).

I've noticed that Rubberduck v2.1.2.2981-pre (and 2968) tends to fill up the RAM pretty quickly, and I was wondering if it was intended behaviour.

Currently, on a 20 MB ACCDB file with ~100 Forms (with approx 50-100 lines of VBA/form, and 3 have ~400 lines) and 5 Modules (1 with 1000 lines, 1 with 400 lines and 3 with <100 lines), I get the following memory usages :

These actions have been done in the order they are written here :

• Idle state (Just opened) : ~40 MB
• VBE Opened (No parsing done) : ~168 MB
• VBE Opened (Parsing Done 1 time) : ~250 MB
• VBE Opened (Parsing Done 5 times, I edited 1 line each time) : ~300 MB
• VBE Closed then Re-Opened : ~320MB
• Form Module edited (1 line) : ~375 MB
• VBE Closed, back to Idle State : ~325 MB

It seems that the memory usage tends to grow pretty fast, and stay high. I'll take a look at the source code, trying to pinpoint that when I have the time, but I mainly wanted to have that possible issue written down somewhere, in case it's already known. I don't have a large enough code base on Excel/Word/PPT to see if it's generic, or Access-specific

When the memory usage gets too high, the MS Access database cannot even close and is left hanging with 100% CPU usage (more exactly, whatever CPU is left for Access to use)

[retailcoder]

Similar: #3347

Closing the VBE doesn't unload anything, it just closes the VBE window (VBE & its add-ins remain loaded).

Rubberduck isn't a lightweight app in the first place, and then it pays in memory what it's not payimg in performance: the depth of Rubberduck's knowledge of the code involves loading every single referenced type library. At any given time, we have 45K objects in memory representing the available symbols and their metadata, in various indexed copies (dictionaries) that are fundamental to resolver efficiency & performance. The user's actual VBA code is tiny in comparison, that's why it makes so little difference. Parsing an empty project caches the metadata of the VBA standard library, the Access object model, and DAO (IIRC).

Memory usage seems consistent across hosts.

CC @bclothier

[bclothier]

@FuSoftware thanks for raising this up! As @retailcoder linked, it is a known problem. However, it took backseat to more critical issues relating with preventing crashes (see project Against the pUnk). Though the progress shows it's only progressing, we've already made major strides forward with help of @MDoerner and @WaynePhillipsEA to stabilize the host and avoid the crashing. But we are not completely done testing all possible holes and not crashing is pretty important to us, so that has been a big focus for last few months since the issue was raised.

That doesn't mean we are going to ignore the issue. On the contrary, we do want to fix this because this deals with the addin's stability and we all like a stable duck we can count on.

I just want to confirm. Are you bringing the RAM issue up not because there's not enough memory but because you find that the host becomes unstable when using it heavily? That was my driving motive for opening the referenced issue #3347. When you consider Visual Studio's footprint, RD isn't that bad in comparison and I think as long it's stable, it should not matter if it takes up to 300 to 500 MB. Do you agree?

[FuSoftware]

@bclothier Here, it makes it sluggish, but it doesn't crash. The only "crach" I can report is the fact that one the RAM usage exceeds ~450 MB after using RD, Access refusesto close at all, and the CPU seems to run endlessly for no reason (I'm sure there is one, but as the RAM usage doesn't change, I really don't understand what it does).

I have a 32 bits 3.25GB computer right now, and VBE/Access tend to become more and more sluggish as times goes. Again, might either be the C# GC trying to do its job as best as it can, or the computer swapping once it reaches 75% of RAM, it's a company computer so I don't have the tools to properly diagnose that.

Another example, I currently have a completely blank Excel 2016 file that is opened, I have not opened the VBA at all (I still have a VBE editor from MS Access opened), and that was enough to raise RAM usage on the Excel process up to 450 MB (which seems a bit high for an empty Excel Worksheet, seeing as how just opening one takes only 100 MB.

Might not be directly related to Rubberduck though, but I have my doubts, I'll have to see how it works behind the scenes.

That's why I said I'll take a look. With VS and its debugger it'll be a bit easier to find where the leaks are.

But in the end, I do agree that first, stability is far more important. The program, even though it can get slow and heavy, is still stable in the way that it doesn't crash (at least it hasn't for me) while running.

Edit : Thanks @retailcoder for the info, makes sense when seen like that ! I wonder if there are way to further reduce the number of objects that need to be stored at all time

[bclothier]

Thank for the background!

Yes, I agree that we don't want it hanging for sure, and it does seem weird that only high RAM consumption seems to do that. My guts is that there's GC going on where it should not be. We try to release what we can and at one point, @MDoerner introduced the ComSafe to help us clean up objects we forgot to clean, as a safety net to prevent crashing or access violations. That would be something to look at to determine if we aren't releasing objects early enough.

Another factor is that if your Access project has more references, that will boost the memory consumption further. I'm presuming that in a Excel worksheet have only 4 references (VBA itself, Excel's OM, the stdole and prolly the Office. But Access application, especially complex one could easily have more than only 5 references, and that will be hard on memory because we have to load those libraries and get metadata for those.