After sometime struggling with databinding performance of VSTO we eventually did an investigation into the root cause.

It turns out that the new transparent proxy feature introduced in VSTO 2005 (not present in VSTO 2003) that is used to support excel COM properties with the hidden lcid attribute is causing serious performance problems.

The Range.Value2 property is one such property if you examine the underlying IDL.

As a simple test we accessed the Value2 property 1000 times using a grid of cells (100x10).

With transparant proxies enabled (<ExcelLocale1033(true)> in AttributeInfo.vb) the performance was attrocious taking a least a minute to perform the operation. More interesting is the reason why. After analysis using VS2005 the team edition's profiling tools and scitech's .Net memory profiler I have discovered the real problem is garbage collector stress due the phenomenal amount of temporary (level 0) memory required to support the transparent proxies in this operation (about 1.8 GB in this test.)

With transparent proxies disabled (<ExcelLocal1033(false)> in AttributeInfo.vb) the performance was much better (about a second). Also a memory analysis revealed no large amounts of temporary memory being allocated.

So for us the decision is made, don't use the new transparent proxy feature in Excel.

We know about the ability to unwrap the proxy by hand, but unfortunately a lot of the standard data binding code is making frequent use of the Range.Value2 property internally where we cannot do the unwrapping unless we write our own databinding, something which I have considered in moments of desperation.

We also know about the use of array assignments to the Value2 property which also obviously speeds things up because of the reduced number of invocations. However again to make real effective use of this approach a custom databinder is really required.

Obviously if we don't use transparent proxies we will have to deal with internationalization issues concerning formula management etc. ourselves. But I would rather deal with those issues than have the current performance issues that result from using the transparent proxies. In any case much of the formula management is done within the data in our solutions to simplify the spreadsheet design.

If I had my way, I feel Microsoft should fix the unacceptable memory performance issues concerning the use of transparent proxies. But I guess the usual will happen and a fix will only arrive if we buy the next version of Office + the next version of Visual Studio.

Dave Stringer