itgenclr007: Fix OutOfMemory in 32-bit Excel with VSTO add-in

Question

A complex query using Invantive Control (VSTO add-in with Microsoft .NET 4.7) retrieves approximately 200.000 rows from Exact Online. This works fine in 64-bit versions of Microsoft Excel 2010 and newer.

After a forced .NET garbage collection at the end of the query reports that approximately 250 MB of memory is in use. During the process, the memory in use varies but sees peaks of 750 MB of memory.

However, on 32-bit versions users often get an OutOfMemory exception during the download of data. Even after refactoring the query to get rid of unused columns and rows as early as possible it still doesn't fit in the available memory.

How can I run such a complex query in Excel 32-bit versions?

Answer 1

Despite that 64-bit versions of Excel have been available at least since Excel 2007, the major of installations is still a 32-bit version of Microsoft Excel.

Most add-in developers have never found a business case justifying support for 64-bit Microsoft Office and in the course of 2010 or so Microsoft changed it's recommendations to use 64-bit versions of Office to 32-bit versions.

The available memory for VSTO add-ins varies per release, but has been decreasing with growing complexity of Excel itself.

Currently, with 1.75 GB of addressable memory space, both Excel and the .NET CLR loaded, you can use approximately 250-300 MB for your .NET code and data at most. The garbage collector does a great job of fitting it all in the addressable memory space, but often this is insufficient.

The amount of available and addressable memory is in general not improved by using a 64-bit Windows version (the common version nowadays) and adding more physical or virtual memory to Windows. It is a limitation of the 32-bit address space design.

Starting with releases of May 2016, Microsoft has set the LARGEADDRESSAWARE bit in the header of Excel.exe. This allows 32-bit version of Excel just like to old 32-bit versions of Oracle and others, to address more than 2 GB of memory.

The knowledge base further documents what versions offer this facility. Given the Excel 2013 has been discontinued, essentially you should use a new release of Excel 2016 in 32-bit to avoid an OutOfMemory exception.

For Office365 subscribers, it is recommended to switch to a channel that includes a sufficiently recent version of Excel 2016.

Although for Excel this increases the addressable memory space to just 3 GB, it increases the amount of memory available for your .NET code and data as a VSTO from 250-300 MB to 1250-1300 MB. An astonishing factor 4-5 increase in available memory space.

Invantive Control displays the addressable memory space in the Help -> Diagnostics window.

For non-LARGEADDRESSAWARE versions of Excel it shows:

But for LARGEADDRESSAWARE versions it shows:

In action with over 2 GB allocated: