How to handle memory management in ExcelDNA

Aug 21, 2013 at 7:50 PM
Hi,

I have created Cache and CacheManager classes within my ExcelDNA Add-In to handle caching of data retrieved from a web service. My concern is that a user of my Add-In may cache a lot of data and experience issues. For that reason, I would like to have Memory Management within the Add-In that will look at how much memory is currently in use by the CacheManager and would free items from the cache based upon the cache date.

My Cache class is as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace ExcelXLL
{
    public enum EnumCacheKey
    {
        EnumCacheKeyGetDeal = 1
    }

    [Serializable]
    public class Cache
    {
        #region Properties

        public EnumCacheKey CacheKey
        {
            get;
            set;
        }

        public DateTime CacheDate
        {
            get;
            set;
        }

        public string DealId
        {
            get;
            set;
        }

        public XDocument XDocData
        {
            get;
            set;
        }

        #endregion

        #region Constructor
        public Cache(EnumCacheKey cacheKey, XDocument xDoc, string szDealId)
        {
            CacheKey = cacheKey;
            XDocData = xDoc;
            CacheDate = DateTime.Now;
            DealId = szDealId;
        }
        #endregion
    }
}
My CacheManager class handles the mechanics of working with the Cache and returns data as XDocument. Within the static class used by my UDFs, I have a property that allows me to access the CacheManager.
    public static class ExcelFunctions
    {
        #region Properties
        public static CacheManager _manager
        {
            get;
            private set;
        }
        #endregion

.
.
.
    }
Would anyone be able to explain how I can manage the memory used by my cache?
Coordinator
Aug 21, 2013 at 8:54 PM
I suggest you investigate the built-in .NET MemoryCache class (http://msdn.microsoft.com/en-us/library/system.runtime.caching.memorycache.aspx). It provides settings that allow you to limit the amount of memory to use for the cache, and you can set (per-item) expiration policies.

-Govert
Aug 21, 2013 at 9:11 PM
Edited Aug 21, 2013 at 9:11 PM
Thanks for the reply Govert,

I have one question though - does the Add-In have its own memory space or does it run within Excel? How does ExcelDNA handle memory in the Add-In?

If it runs within Excel, I would need to know how much memory is available to Excel. If it is just the Add-In, this helps alot.

Thanks,

Lee
Coordinator
Aug 21, 2013 at 9:22 PM
Edited Aug 21, 2013 at 9:23 PM
The add-in runs in the Excel process, but in a separate .NET AppDomain.
Excel-DNA has no special memory management apart from the managed <-> native marshaling, which will generally use only small amounts of memory.

A 32-bit Excel process will have a memory limit of ~2GB, while a 64-bit process is not likely to run out of memory. Remember that Windows will start swapping to disk if there is pressure on the RAM.

-Govert
Aug 21, 2013 at 9:35 PM
Hi again Govert,

I just researched the MemoryCache and it is useful in setting limits on each cached item. What I do not understand is how to check that Excel is not running out of memory as well. Let's assume the users are running a 32-bit version (although from what I read Excel 2007 also limits to 2 GB regardless of whether it is 32 bit or 64 bit). Let's also assume that the user has a lot of data in the workbook in addition to what is cached. If resources are getting low from other events (in Excel or on the user's computer or both), but each cached item is smaller than the physical cache limit, it seems as though the cached items will not be cleared as they do not exceed their limit. Is my understanding incorrect or do I need to do more?
Coordinator
Aug 21, 2013 at 10:26 PM
Edited Aug 21, 2013 at 10:29 PM
Hi Lee,

There is no 64-bit version of Excel 2007. The 64-bit versions of Excel 2010 & 2013 are not limited to 2GB.

Running the normal 32-bit Excel close to the 2GB limit is a bad idea. Excel does not suddenly break, but things start to go wrong in weird ways.
If you're worries about this you should do some testing.

I guess you could use the WIN32 API to get information about how much memory the Excel process is currently using - something like GetProcessMemoryInfo should work. Interpreting the results might be a bit tricky and take some experimentation.

The general case where your cache items might be huge, the workbooks might take up any amount of space, and you want a cache as large as possible, is probably very hard to get right. Won't it make sense just to limit the cache to 100MB or something, and not worry further? There is anyway nothing you can do to prevent your users from crashing Excel by putting too much data into the sheet (whether your add-in is loaded or not). It's just good if the cache is not unbounded.

-Govert