Help with RTD handling large topics (30K cells)

Add-in Express™ Support Service
That's what is more important than anything else

Help with RTD handling large topics (30K cells)
We have a XLL/COM/RTD solution based on your examples that handles <10K formulas/cells fine, but struggles with larger requests 
psycho




Posts: 18
 Joined: 2013-02-25
Hello,

I have a XLL/COM/RTD solution based on your examples (from perhaps 4 years ago) that handles <10K formulas/cells fine, but struggles with larger requests.

To give some context, we have a udf that takes say 3 params: ABC(identifier, date, source). That triggers a request fetch from our back-end servers and returns data to the topic coded by {String01:identifier, String02:date, String03:source}. To keep things easier to work with we decided in this step to also store the same information into a C# dictionary.

It seems that when we fill a large amount of cells with formulas (say 20K),
1) Excel spends one cycle of "Calculating" to trigger/map and put placeholder data (DefaultValue) while marking the cell as dirty (not particularly fast, but manageable), but then
2) Excel spends many more cycles of "Calculating" to execute UpdateTopics() which sometimes takes 10-20 minutes to complete. Back-end service provides these 20K results very fast and is not the bottleneck.

This behavior might be because we have the RTD server on interval of 600ms to grab and chunk requests on the fly, but even when I disable this behavior and explicitly set EnableCalculate and explicitly call UpdateTopics() when I know the back-end fetch is already complete, it remains slow.

Can you think of anything that could help me resolve this bog? I'm not opposed to drastic design changes, so I'm sure anything you think up will help. Do you think it's my dictionary lookup? Should I continue pursuing explicit EnableCalculate and UpdateTopics for huge chunks (disable Interval when in this mode)?

Much thanks!
Posted 28 Feb, 2019 10:27:31 Top
psycho




Posts: 18
 Joined: 2013-02-25
Can Topics become very large and still perform well updating? Should I try to chop them up more and manage how large they get myself?

I'm really targeting better performance for about 30K cells of pretty much the same function calls.

Thanks again!
Posted 28 Feb, 2019 10:37:20 Top
psycho




Posts: 18
 Joined: 2013-02-25
What I'm noticing is that if I have a sheet with filled results for 20K function calls (all mapped currently to one master Topic) and I go to add a new call in cell 20,001 and it in turn calls UpdateTopic(), I'm getting a long "Calculating" phase that looks like Excel is trying to

1) update that one cell (which it does instantaneously), but then also
2) validate all data/cells that are tied to the same topic (which probably have not changed).

Is this true?

If yes, managing many smaller Topics should work for me if I target updates better. Alternatively, is there a way to turn off the validation behavior based on some window of time (I'm surprised there's any reconciliation step as the 20K resolved cells is not marked as dirty or anything).
Posted 28 Feb, 2019 11:25:07 Top
nwein




Posts: 577
 Joined: 2011-03-28
Just throwing my two cents here - have you tried using the native Excel RTD function to see how it performs with large amount of data? i.e not your (i'm assuming) wrapped UDF.
Also, looking at your "I'm not opposed to drastic design changes", have you tried implementing yourself the IRtdServer in your ADXRTDServerModule?
I'm asking cause that's the way we went with and it seems to be more manageable than relying on the ADXTopics infrastructure (no disrespect to ADX).
Posted 28 Feb, 2019 15:51:21 Top
psycho




Posts: 18
 Joined: 2013-02-25
Thanks nwein! I'll try both of your suggestions next. I really appreciate it.

have you tried implementing yourself the IRtdServer in your ADXRTDServerModule?


No, I'm going to start digging/reading into this. The Excel Add-In isn't a core product at the company I work for, so usually I only get to work on the project in spurts (a few weeks a year, really). The IRtdServer interface is new to me, so I'm super thankful you chimed in to help. Thanks again.

Cheers~
Posted 01 Mar, 2019 10:02:47 Top
Andrei Smolin


Add-in Express team


Posts: 18844
 Joined: 2006-05-11
Hello,

I wouldn't expect that RTD Server would be fast with that many topics. Consider putting data in the worksheet using Range.Value = {an array of values here}. See e.g. https://www.add-in-express.com/creating-addins-blog/2011/09/29/excel-read-update-cells/.


Andrei Smolin
Add-in Express Team Leader

Thank you, Nir!
Posted 01 Mar, 2019 10:27:40 Top
psycho




Posts: 18
 Joined: 2013-02-25
You were right! Using native RTD() and not my wrapped UDF handles 40K+ like a champ with no reconciliation/excess "Calculating" phases.

I'll look into the interface next :) Sounds very promising. Thank you so much.
Posted 01 Mar, 2019 11:10:29 Top
psycho




Posts: 18
 Joined: 2013-02-25
I wouldn't expect that RTD Server would be fast with that many topics. Consider putting data in the worksheet using Range.Value = {an array of values here}. See e.g. https://www.add-in-express.com/creating-addins-blog/2011/09/29/excel-read-update-cells/.


Awesome, thanks Andrei. You've helped me a lot these past six years! Good to hear from you again :)

Also, much thanks again nwein! I think I know what I need to do.
Posted 01 Mar, 2019 11:12:31 Top
nwein




Posts: 577
 Joined: 2011-03-28
You're welcome :)
Posted 01 Mar, 2019 11:58:01 Top
Andrei Smolin


Add-in Express team


Posts: 18844
 Joined: 2006-05-11
You are welcome!


Andrei Smolin
Add-in Express Team Leader
Posted 04 Mar, 2019 05:01:03 Top