Benchmarks

[lukedays]

I'm trying to gather benchmarks for the main possible ways to create Excel UDFs. I included xlOil (great performance!) and plan to do a post on LinkedIn.
I tried to compile the C++ version of xlOil, but ran into several errors. Do you have any tips?

https://github.com/lukedays/excel-benchmarks

[keithalewis]

Hi @lukedays You may want to try https://docs.microsoft.com/en-us/cpp/parallel/concrt/parallel-algorithms?view=msvc-170#parallel_reduce in your C++ example.

[lukedays]

Good call! I'm at the start of the long road of learning C++ haha.
I updated the tests, it went 1-2 ms faster. I guess there is a minimum overhead in calling xll's that is hard to beat. I plan to add other tests since SUM() seems to be super optimized.

[cunnane]

I wonder how SUM performance compares to just writing a loop in C(++) without any Excel calls? Also there seems to be a fair amount of overhead in the parallel library the performance figures here are 9250ms vs 5726ms for a 4 CPU machine - you'd expect hand-written threads to scale a bit better with core count. In practice, using the parallel library in Excel UDFs might not always be desirable since it may conflict with Excel's own threading.

[lukedays]

Just did a simple test, changed the C++ function body to empty.

Name	Excel SUM(...)	Do Nothing	C++ (xlladdins)	C# (Excel-DNA)
Median	3.2	9.8	10.1	12.2

Also did another test, by inlining the array generation - e.g. SUM(SEQUENCE(1000,1000)). Then the results become very close. So I guess the additional time is due to conversion from Excel.Range to FP.

Name	Excel SUM(...)	Do Nothing	C++ (xlladdins)	C# (Excel-DNA)	Python + Numpy (xlOil)
Median	15.9	14.7	15.0	18.2	46.8

[cunnane]

Very interesting! So the SUM function isn't magically quick, it's just the overhead is 10ms. The xlOil timing makes sense in that it has to do another overhead step to get the array into numpy, then the summing is done in C.

Another test which might be interesting is to take the argument as an XLOPER instead of an FP struct.

[lukedays]

Yes, I'll do more tests!

[cunnane]

@lukedays what build errors are you getting? Are you building the whole of xlOil from source? I haven't updated the C++ binary releases in a while... let me do that...

[lukedays]

Yes, I tried to do a static build of TestAddin with an old release (0.12). There were some headers missing (ATL, etc). But I guess the plugin method should be easier. Let me know when you post the new binaries so I can try again.

[cunnane]

Ah OK, your need to install the ATL headers as part of your Visual Studio install under the C++ choices. However they're only required to build xlOil-COM.lib, but for purposes of this test you don't need that library, just xlOil-XLL.lib and xlOilHelpers.lib. The TestAddin project does test COM stuff like ribbon creation so needs to link the COM parts.

[lukedays]

Yes, I tried to do a static build of TestAddin with an old release (0.12). There were some headers missing (ATL, etc). But I guess the plugin method should be easier. Let me know when you post the new binaries so I can try again. Em sex, 26 de ago de 2022 12:15, Steven ***@***.***> escreveu:

…

@lukedays <https://github.com/lukedays> what build errors are you getting? Are you building the whole of xlOil from source? I haven't updated the C++ binary releases in a while... let me do that... — Reply to this email directly, view it on GitHub <#6 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAC6OUYY3TUSNSXZSUZWB3DV3DNQFANCNFSM57TSAK5A> . You are receiving this because you were mentioned.Message ID: ***@***.***>