RTD server accumulating memory over each run.

Jun 3, 2014 at 10:33 AM
Hi everyone,

I am in need of some major assistance, please help me programming god :(. I developed a rtd server for excel that feeds from my local sql database. My memory usage of excel keeps increasing exponentially after each refreshdata run (30mb each run) and will ultimately crash when it hits 1gb. I have doubled and triple checked my codes to make sure that my variables are set to null and it doesn't solve anything. Switching from automatic calculation to manual calculation would make the memory usage the same and not decrease it. I suspect its either my calculation links to the rtd cells / the rtd cells that is causing it /
sql datatables taking up space.

Is there a way to clear existing excel memory or the rtd server or functions in general? Thanks so so much in advance.
Coordinator
Jun 3, 2014 at 11:55 AM
Hi,

If it's the RTD part, you should be able to reproduce the memory usage pattern without having the SQL Server connections involved.
You might also investigate some of the .NET memory profiling tools - the built-in Visual Studio tools would be a start: http://blogs.msdn.com/b/dotnet/archive/2013/04/04/net-memory-allocation-profiling-with-visual-studio-2012.aspx

If you're able to make a small standalone project where your database is not involved, and where you still run into the memory trouble, I'm happy to have a look.

Regards,
Govert
Jun 4, 2014 at 11:45 PM
Hi,

Thanks govert for your reply! I finally "fixed" it but it is not the rtd functions that are causing it. Dynamic functions in excel that uses sql accumulate heap due to the rtd's constant refreshing (for example =func1(today()), resulting in the excel app growing exponentially. The only ways I could solve this is to close the sql connection and reinitialise this after x amount of time or changing the function to "=func(5/5/2014)" so that the excel heap doesn't accumulate. If there is any other way that I am missing, please feel free to contribute. :)
Coordinator
Jun 5, 2014 at 7:21 AM
I don't understand why the dynamic functions would use more and more memory.
Can you recreate this with simple functions that don't involve SQL?

-Govert
Jun 5, 2014 at 8:34 AM
I have no idea on that too... treating it as a can of worms at the moment.
I can't really use simple functions as I have a huge legacy SQL database that my company is using and they really want to use excel. Tried from vsto and varies other random stuffs before stumbling onto exceldna which is currently what I am using.
Coordinator
Jun 5, 2014 at 8:54 AM
If your add-in is using more and more memory when your functions recalculate, until Excel crashes, then there is a bug somewhere.
i'm suggesting that you make simple functions and check that they don't have the bad behaviour. Then make them more complicated again step-by-step and check for problems (bad memory usage) at every step until you have the full functionality again. That way you can find the place where things go wrong.

Anyway - good luck with your add-in.

-Govert