Session State vs. Global State in UDFs

I already posted about what sort of functions can be created for Excel Services. Now I wanted to add a bit of information about Session State vs. Global State with Excel Services UDFs. As you know, Excel Services allows multiple users to execute multiple workbooks at the same time. For this reason (and others), the Excel Client UDF model does not fit 100% when taken to the server.

On the client, an Addin can listen to events and know what a workbook has been closed and decide to clear some cache items. No such events exist on the server – furthermore, the same workbook can be opened hundreds of time on the server. So what tools do we provide for handling such cases?

As you recall, Excel Services supports only classes that are non-abstract and have a parameterless constructor. The reason behind this is that for every session that will be initialized on the server, we will instantiate an instance of your UDF class. This will allow you to have session-only caching if you choose to.

In the DNS Lookup example I posted about, I showed how we can call Web Services and return the results of these calls to Excel Services. You can easily imagine a case where you would want to cache these results so that consequent calls asking for the same name will not cause an actual Web Service call to be made.

Session Level Caching

Doing session-level caching in your UDF is incredibly simple and straightforward, you simply need to use your UDF class non-static members for storage of the cache. Since a new instance of the class is created for each new session, you will get a fresh copy of the cache each time a user opens a workbook on the server.

When Excel Services is done with the session and needs to close it, it will check to see if your UDF class inherits from IDisposable. If it does, it will make a call to your Dispose() method allowing you to do what clean-ups you deem necessary (though, unless you have native resources, there’s no real reason for you to do this – the GC will take care of everything for you).

Global Caching

For global caching, use static members in your class to store the cached information. Keep in mind though that Excel Services is a server and as such is expected to be up for very long periods of time. You may need to add mechanisms that will age your cache and get rid of memory that is not being used – otherwise, after running for a long while, you may end up choking the server with memory that is not really needed.

Comments

• Anonymous
  May 23, 2006
  The Global Caching mentioned works of for a single Excel Services Back End instance (e.g. one back end Excel Calculation Server).
  
  For multiple back end machines, a central external database can be used to store/maintain/retrieve data making it truely global to the "Web Farm".
  
  This type of implemntation can be used in conjunction with either Session Level or Global Caching (XBOX and Excel Server:-)
  
  Dan
• Anonymous
  May 24, 2006
  Partial solution for using RTD servers in Excel Services - Part 2.
  In this part, the solution implementation is discussed and presented. Also, an attachment of the code that makes it work.
• Anonymous
  August 03, 2006
  I have made a few posts about UDFs over the past couple of months. One of the things I neglected to explain...
• Anonymous
  September 01, 2006
  Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on...
• Anonymous
  September 28, 2006
  Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
  One...
• Anonymous
  October 30, 2006
  Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good. One of