Excel-DNA / Excel-DNA/ExcelDna
Slow async UDF
- 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
Assessment
This issue has not been assessed yet.