Excel RTD server Topic removal

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

Excel RTD server Topic removal
Some performance issues from sequentially rendering lots of data 
Gavin Howard




Posts: 14
Joined: 2016-12-01
ADX,

We're currently experiencing some cumulative performance issues using the ADX RTD server in Excel. In our addin we have created an XLL custom user formula over the top of an RTD server to make requests to our webservice and render the data in the workbook.

What we have discovered recently whilst creating a custom Excel template for one of our clients is that as we request more data from our webservice, the performance of requesting new data becomes gradually slower until it becomes unusable ( > 50,000 requests ).

We're actually replacing the formula in the workbook with new ones by changing the parameters dynamically in our custom excel template. However it seems that the topics for the old formula still remain in the collection internal collection. We can see that the "calculating" state of excel is increasingly longer each time we do this parameter change operation.

We have discovered a temporary workaround for our excel template by doing a find/replace on the forumla name and changing it to one that doesn't exist. This has the effect of disconnecting the topics and eventually calling dispose on the RTD server itself (which we can see causes a CPU spike). After this the performance returns back to normal.

We notice that another user had a similar issue:
https://www.add-in-express.com/forum/read.php?FID=5&TID=12525

However I tried to call dispose on the topic as the Disconnect event was fired as suggested, however it had the effect of making our addin unstable and we were unable to request data for that parameter set again as the return would just display "#VALUE" error.

Would it be possible to suggest any other workarounds we could build into our addin itself to solve the performance issue? Alternatively could you look at your RTD code to see if the unused data can be removed to speed it up?
Posted 04 Dec, 2019 10:26:50 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Gavin,

Gavin Howard writes:
We're actually replacing the formula in the workbook with new ones by changing the parameters dynamically in our custom excel template.


How do you do this? It looks like Excel doesn't know about your actions and it doesn't delete the topic(s) because of this.


Andrei Smolin
Add-in Express Team Leader
Posted 05 Dec, 2019 07:44:25 Top
Gavin Howard




Posts: 14
Joined: 2016-12-01
Andrei,

Perhaps that was not quite the best way to phrase it - we're actually referencing parameters in another cell in another sheet and when the user updates these cells it has the effect of changing the formula. So we're not doing anything clever here, it is just standard Excel functionality.

For example:
=MYXLL(Charts!$D$6, Charts!$C$6, ...)

I would have thought that the data resolved for the previous parameter values would be removed from the RTD internals? But this does not seem to be the case.
Posted 05 Dec, 2019 08:31:36 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Gavin,

No problem. It's great that a correct definition is found.

Is the Dispose() method called on the topic in this case?


Andrei Smolin
Add-in Express Team Leader
Posted 06 Dec, 2019 08:06:00 Top
Gavin Howard




Posts: 14
Joined: 2016-12-01
Andrei

I have put together a sample project for you along with a sample workbook and steps to reproduce the issue. I have sent this package over to support@add-in-express.com

Please can you take a look and confirm you can see the pattern of behaviour we observe. If you do observe this, can you look into what is causing it and suggest either fixes we could make in our code or otherwise find the source of the issue.

Gavin
Posted 09 Dec, 2019 06:50:23 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Gavin,

Thank you. We've received the project and now we study it. I suppose I'll respond tomorrow or the day after tomorrow.


Andrei Smolin
Add-in Express Team Leader
Posted 09 Dec, 2019 07:14:07 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Gavin,

You do not handle the ADXRTDTopic.Disconnect event. Accordingly, your collections can only grow and in this way you need to deal with more and more and more data.

A minor issue. I recommend that you move initialization of complex-type class-level variables (such as logger) to an event of the module. Say, to the RTDInitialize event. This will let your RTD server module to open at the design time.

private SomeType someVar = new SomeType(); -- > private SomeType someVar; // initialize in RTDInitialize


Andrei Smolin
Add-in Express Team Leader
Posted 09 Dec, 2019 09:34:43 Top
Gavin Howard




Posts: 14
Joined: 2016-12-01
Andrei - Apologies, as mentioned in my first post we did try this. Updated the sample project to include these methods. The issue can still be observed.
Posted 09 Dec, 2019 10:06:03 Top