0

I am writing an Excel addin using ExcelDNA and creating a UDF(User-Defined Function).

The UDF establishes a connection to a server and that server connects to an SQL Database to retrieve data and send it back to the client.

However, this UDF will be expected to be called hundreds of times in one worksheet.

I am trying to find a way to reduce the number of requests to the server.

As of this moment, this is how the template looks like (simplified):

    ITEM     | JAN 2016 | FEB 2016 | MAR 2016 |
---------------------------------------------------
   ITEM 1    |    UDF   |    UDF   |   UDF    |
   ITEM 2    |    UDF   |    UDF   |   UDF    |
   ITEM 3    |    UDF   |    UDF   |   UDF    |
   ITEM 4    |    UDF   |    UDF   |   UDF    |
   ITEM 5    |    UDF   |    UDF   |   UDF    |
   ITEM 6    |    UDF   |    UDF   |   UDF    |

So, right now, this would send 18 requests in total.

I would like to try to reduce the number of requests to 6.

The way I am thinking of doing that right now is that for each item, whichever request gets sent first will get the data for all the months listed of that item. Then the 2nd request for the same item would just wait for the result of the first request.

For example:

For ITEM 1, If the UDF in the JAN 2016 column gets called first, it will request data for all the months of ITEM 1 and then store that data somewhere for the other requests to take from.

Then when the 2nd request comes (let's say MAR 2016 is next), that UDF will wait for the result of the UDF of JAN 2016. This way, the UDF of MAR 2016 will not have to start another connection to the server.

However, I have no idea how to implement this. I have heard of the keywords await and async, but have no experience with using them.

Any other solutions to the problem are also welcome.

Steven John Lally
  • 143
  • 3
  • 14
  • Consider removing `async-await` tag as it is not related to batch processing and your question in particular. – cassandrad Aug 26 '16 at 12:26

2 Answers2

0

Asynchronous calls won't help you reducing the number of calls, it will just start more parallel calls. Does the number of calls cause you any problems? Maybe the "best practices" here helps you out: C# Data Connections Best Practice?

Community
  • 1
  • 1
Mr. T
  • 65
  • 1
  • 8
0

Async functions and batching can help you. Basically you collect all the requests you want to make to the back-end for a short period of time (say half second) and then fire a single call to the back end. When the result comes back, you complete the async call for each request.

This Gist has a complete implementation of the async and batching parts, you just have to supply the batch evaluation function: https://gist.github.com/govert/1bf0a1026ec3aaee19a8

The Excel-DNA Google group is probably better for any follow-up questions about this example.

Govert
  • 16,387
  • 4
  • 60
  • 70