How to pool all RTD calls at excel startup?

I have an RTD server that gets the values from a realtime source. The problem is that the users have pretty large excel sheets close to 20,000 RTD formulas. So when the user opens the sheet, all the RTD formulas get fired resulting in sending 20,000 queries to the server. This works for now, but the server can perform much better if i can group the queries and s开发者_运维百科end it to the server.

My idea was to maintain a flag. When the calculation starts, the flag will be set to false, and when the calculation ends i can reset it. When an RTD formula is called, if the flag is unset, i will not send the query to RTD server, but pool it. When the flag is set to true i can combine the pooled queries and send it to the server

I am not sure how to get the notification as when excel starts and stops calculating. Please help. Also if you know any other approach for solving this problem, it would be great. I am using Excel 2007, C# 3.5

Please help. Thank you very much. Rashmi

Thanks,

Since you're using RTD, I wonder if this could work:

You make calls to the back-end in timed batches. You start a timer in the first RTD call - a short time, maybe 500ms, then build up a batch of work from all the calls made to your RTD server until the timer expires, then send the batch to the back-end and await the response, while starting a new batch on the client. When the batch response comes, you notify Excel that the topics have been updated, and when Excel calls RefreshData you return the individual items out of your batch response. This way your batching uses the async-ness of RTD effectively, but you are not tied to Excel's recalculation events.

Hope this makes sense.