Excel-DNA / Excel-DNA/ExcelDna

Slow async UDF

Open
#232 5 comments 1 reaction 0 assignees View on GitHub
Dominant language
C#
Stars
1.5k
Forks
292
Avg merge
20d 12h
Merged PRs (30d)
1

Description

Hello, while using Excel DNA async UDFs we experienced some performance issues. The main problem seems to be when you are using UDF directly in the cell and with larger quantity. There is a simple sample of UDF we tested on:
```
[ExcelFunction(Description = "Validate", IsThreadSafe = true)]
public static void Validate(object value, ExcelAsyncHandle asyncHandle)
{
asyncHandle.SetResult(1);
}
````
When you create sheet with 100k values of `=Validate("dummy")` without addin added to Excel and then you reopen sheet with addin activated, it takes about 30s to calculate. Also, there seems to be theshold around 25k values, because sheet with 20k values is calculated almost immediately (<1s). So steps to replacate issue are:

1. Create sheet with 100k values of `=Validate("dummy")`.
2. Create addin with UDF from sample.
3. Add addin to Excel.
4. Open sheet created in step 1.

Additional info:
Only UDFs in sheets seem to be slow. Data validation and conditional formating with named formula in combination with async UDF are quite fast even with 100k or 2M values.
- Data validation: limited to 255 invalid cells, but even with formulate that makes evaluate every cell to a valid value (and therefore all cells are evaluated), it is quite fast even with 2M values, benchmark results:
@ 2M unique input values and 1 thread (see last point) on virtualized Win 7 and Office 2016 x64 on Xeon E5-1650v4 enviroment
- async UDF: 35 secs (with instant reply, more precisely handle.setResult(counter % 100) directly from UDF method)
- sync UDF: 21 secs
- conditional formating: seems to be calculated for visible area and some (actually a lot, like 10k+) values around only. Conditional formating is recalculated on every sheet update even if some value outside the conditional formating area is changed and even if formula is not marked as volatile. Very fast even (no UI freezing while scrolling) at 10% zoom level and full screen.
- both (data validation and conditional formating) seems to be limited to one thread (just fact, but the resulting performance is fast)

Benchmark results of UDFs in sheets:
@ 2M unique input values and 3 thread (3 available CPU cores in OS) on virtualized Win 7 and Office 2016 x64 on Xeon E5-1650v4 enviroment
- async thread safe UDF: N/A (5% complete after 120 secs, with instant reply, more precisely handle.setResult(counter % 100) directly from UDF method)
- async thread safe UDF with Excel multi threading manually disabled: not faster, probably 3x slower as single threaded
- sync thread safe UDF: 2.5 secs
- both times are without document open which takes about 6 secs

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.