Memory leak?

Jul 13, 2012 at 9:51 AM

Hello,

I have create an application which will update excel very quickly using the ExcelReference class. This seems to leak when you use strings. Source:

 

namespace DnaTest
{
    public class EAction
    {
        private readonly object[] _o;
        private long _t;
        private readonly ExcelReference _ref;
        public EAction(int row,IntPtr sheetId)
        {
            _t = 0;
            _o=new object[2];
            _ref = new ExcelReference(row, row, 0, 1, sheetId);
        }
        public void Action()
        {
            DateTime dt = DateTime.Now;
            _o[0] = dt;
            _o[1] = _t.ToString();
            _ref.SetValue(_o);
            _t++;
        }

    }
    public class AutoReg : IExcelAddIn
    {
        private int _n = 5;
        private int _row = 1;
        private readonly object _lock = new object();
        private IntPtr _sheetId;
        public void AutoOpen()
        {
            Debugger.Launch();
            ExcelAsyncUtil.Initialize();
            ExcelReference sheetRef = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "sysTimers");
            _sheetId = sheetRef.SheetId;
            for (int j = 0; j < 5; j++)
            {
                var t = new Thread(Start);
                t.Start();
            }
        }
        public void AutoClose()
        {

        }
        private void Start()
        {
            int row;
            IntPtr sheetId;
            lock(_lock)
            {
                row = _row++;
                sheetId = _sheetId;
            }
            var a=new EAction(row,sheetId);
            for (;;)
            {
                ExcelAsyncUtil.QueueAsMacro(a.Action);
                Thread.Sleep(_n);
            }

        }
    }

}

This will consume memory very quickly. When you change the line
            _o[1] = _t.ToString();
with
            _o[1] = _t;

It will be ok. 

The memory in the marshaller seems to be freed, so I have no idea what could be causing this.

Can you shed some light on this?

Thank you,
Arno de Vries






Coordinator
Jul 13, 2012 at 11:49 PM
Edited Jul 14, 2012 at 12:47 AM

Hi Arno,

Thanks for the nice question. I can reproduce what you report but don't have an answer yet.

I'm pretty surprised it works as well as it does. The ExcelAsyncUtil.QueueAsMacro is not meant for this kind of abuse - for example I don't try to reduce the number of Windows messages, so every call you make causes a new message to be posted to the Excel message queue, possibly causing problems (like dropped calls) if there are too many calls outstanding. But it's very nice to see how it all behaves under pressure - thanks for having a look and sending the code.

[To be sure, for interactive use, the RTD server is a much better choice. And for poking Excel with data for processing, there is of course no point in using multiple threads.]

Trying to understand the memory issue, I've tried to add some strategic GC.Collect()s, just to see if that might be an issue, but it doesn't seem to make a difference.

My next guess would be that the memory might be used by some queue growing and not being serviced properly because of messages being dropped due to the high rate. But I'll have a look again later in the weekend and write back if I find anything.

-Govert

Coordinator
Jul 15, 2012 at 1:35 PM
Edited Jul 15, 2012 at 1:49 PM

Hi Arno,

I've explored a bit more, but don't yet have an answer for you. But the memory usage sometimes seems to stabilise after a while - say 10 minutes or so. Do you find that too?

It does not seem to be related to setting strings in a simple way. You can replace the relevant line in your code with:

_o[1] = new string('A', ((int)_t * 100) % 128 );

 which creates strings of different lengths, and the memory use soon stabilizes. So I doubt it is a simple memory leak in the Excel-DNA code. Another check you can make is just to use (_t % 1024).ToString(). So it seems to require a large number of different strings for this behaviour to arise.

It also does not seem to be related to the ExcelAsyncUtil or the message queuing in any way. Just making a macro that writes values into the sheet behaves the same way - the memory counters grow when setting with new long.ToString() values, but not when setting long values directly.

Next thing we could check is whether this happens even when driving Excel with the C API directly from native code. (I've checked VBA and there's no issue).

I'm ascribing this to an Excel string management feature, until I see further evidence.... 

-Govert

Coordinator
Jul 15, 2012 at 4:24 PM

Another small data point: If you run the macros to fill in strings up to, say 100 000, and then restart your counter, then the next run of strings will not increase the memory used much until you hit 100 000, and will then start increasing again.

So I'm pretty sure it's some kind of string caching happening in the .NET runtime or Excel. My money says it's Excel but we need a native check.

-Govert

Jul 16, 2012 at 10:06 AM

Hi Govert,

 

Some background: I have used the ExcelAsyncUtil code for a timer which usualy runs at 1 second intervals or slower. I write the time to a cell as a string, and in the worksheet_Change in VBA I do some processing. I have put this in a duration test which ran for several days and noticed a constant increase of private bytes, which will probably make Excel unusable after a few weeks.

In order to find the cause I have created above test which I have ran for several hours. The private bytes use never stabilized. I have  just tried to send identical strings, and in that case the private bytes use does not rise! I think you are right and it is a excel string handling issue. (maybe only when using xlSet?) It can be some sort of cache, but in that case it is not very efficient (6 million updates resulted in about 250 MB of memory use), and it seems its memory use is not limited .

 I now only send the raw OleDateTime value in my timer as a workaround, and this works ok.

Thank you,

 

Arno de Vries

Coordinator
Jul 16, 2012 at 3:07 PM

Hi Arno,

I'll investigate this further when I get a chance - at least to narrow down between Excel-DNA / .NET and the Excel C API.

For your use it sounds like an RTD server would be much better.... I have yet to check whether this has the memory leak issue.

-Govert

Coordinator
Oct 5, 2012 at 10:27 PM

OK - this seems like an issue with Excel, unrelated to Excel-DNA. I can reproduce with a C++ .xll add-in, and even from VBA with this code:

Sub MemLeak()
    Dim val As Long
    val = 0
    While True
        [A1] = "X" & val
        val = val + 1
        If val Mod 1024 = 0 Then
            DoEvents
        End If
    Wend
End Sub
-Govert