Caching database data in Session - getting the balance right

Question

If you cache data from your database in ASP.NET Session then you will speed up subsequent requests for that data (note: depending on the serialization/deserialization cost of the data concerned), at the expense of memory load in IIS.

(OK, this is probably a simplification of the reality of the situaiton - feel free to correct or refine my statement above)

Do you use any rules (simple rules of thumb or otherwise) to decide when it is appropriate to cache in Session?

Update

Can use of Session for storing read-only data without very well thought out rationale simply be considered another case of Premature Optimization? (and therefore, bad)

Answer 1

Keep in mind with caching in session if you use the InProc mode, you are limiting your scalability unless your code will go back to the DB when the session is empty. You will be forced to use a single server or pin your user to a particular server.

If your using Session State Server this doesn't apply, and if your using SQL to store session state then using session for caching is pointless.

Edit

Any advice around this topic is subject to your specific environment. As you stated, a very complex sql query might benefit from being cached even when using SQL Session State. I think the most important thing is first make your application perform the functions and achieve the business requirements. Then go back and test and optimize the application to handle the load you expect.

Edit 2

Based on your update no I don't think this is true. In one of my ASP.Net Applications I am using session state to store a complex object model that the user is then manipulating and modifying. We are using AJAX and so we have a lot of short communication to server as user updates the object. Keeping the object in session was done as a convience. The object performs a lot of customized calculations to generate different data points, so doing this on the server was ideal as opposed to trying to replicate the code in the servr and in javascript. Also keeping it in session lets us have an undo function very easily.

And Yes I know we sacrificed scalability, but I welcome the day when I sit down with my boss and explain we have a problem because we have too many users (And I know he would too).

So I think the question is why are you storing data in session is it for convience and to provide access to transient data while the user is logged in? That is different then storing it there for caching. One thing to remeber with caching is how are you going to flush the cache? How do you invalidate it? I don't session state is built to handle this.

Edit 3

Back at it well read only data, user specific, expensive to load from DB, go ahead cache it in session. You can write your code so if it's not in session then you hit the DB. Have a nice little helper class that does this hidding it from your web app that your even using session and you should be good. Nice thing about hidding where you store it from your web if you find you run into issues you only have one place to change it.

Answer 2

Given that it is very hard to guarantee that data in a session gets cleared up in a reasonable time frame, I would be very wary of cashing anything more than the odd integer there. When you get up to a large scale you will either hit memory issues or have all your users get session time out errors. Remember that unlike the Cache object sessions don't get killed off when memory is low.

Also as Josh says if you go to multiple servers and need to use either a Database or Session State Server your session object will need to be serialisable. In this case the cost of serialising and de serialising is likely to be worse than the cost of a well optimized query.