DAL, Session, Cache architecture

I'm attempting to create Data Access Layer for my web application. Currently, all datatables are stored in the session. When I am finished the DAL will populate and return datatables. Is it a good idea to store the returned datatables in the session? A distributed/shared cache? Or just ping the database each time? Note: generally the number of rows in the datatable will be small < 2000.

Additional info:

Almost none of the data is shared. The parameters that are sent to the SQL queries are chosen by the user. The parameter values available to the user are based on who the user is. In most cases it is impossible for two users to run the same sql queries. However, the same user can run the same query more than once.

More info: Number of concurrent users ~50,000

Important info: In 99% of the cases no two users will have the same data/queries, however, the same user may run the same query/get the same data multiple times.

Thanks

Storing the data in session is not a good idea because:

1. Every user gets a separate copy of the same data - enormous waste of server memory.
2. IIS will recycle a session if you fill it with too much data.

I recommend storing the data tables in Cache, and also populating each table only when first requested rather than all at once. That way, if IIS starts reclaiming space in the cache, your code won't be affected.

Very simple example of fetching on demand:

T GetCached<T>(string cacheKey, Func<T> getDirect) {
    object value = HttpContext.Current.Cache.Item(cacheKey);
    if(value == null) {
        value = getDirect();
        HttpContext.Current.Cache.Insert(cacheKey, value);
    }
    return (T) value;
}

EDIT: - Question Update

Cache vs local Session - Local session state is all-or-nothing. If it gets too full, IIS will recycle everything in it. By contrast, cache items are dropped individually when memory gets too low, so it's much less of a problem.

Cache vs Session state server - I don't have any data to back this up, so please say so if I've got this wrong, but I would have thought that caching the data independently in memory in each physical server AppDomain would scale better than storing it in a shared session state service.

The first thing I would say is: cache is not mandatory everywhere. You should use it wisely and very specially on bottlenecks related to data access.

I don't think it's a good idea to store 1000 different datatables with 2000 records anywhere. If queries are so dynamic that having the same query in a short period of time is the exception then cache doesn't seem a good option.

And in relation to a distributed cache option, I suggest you to check http://memcached.org . A distributed cache used by many big projects around the world.

I know Velocity is near, but so far I know it needs Windows Server 2008 and it's something very very new yet. Normally Microsoft products are good from version 2.0 :-)

Store lookups/dictionaries - and items that your app would require very frequently in Application or Cache object; query database for data that depends upon the user role.

--EDIT--

This is in response to your comment.

Usually in any data oriented system, the queries run around the facts table(or tables that are inevitable to query); assuming you do have a set of inevitable tables, so you can use Cache.Insert():

1. Load the inevitable tables on app startup;
2. Load most queried tables in Cache upon table request-basis;
3. Query database for least queried tables.

If you do not have any performance issues then let SQL handle everything.

Storing that amount of data in the Session is a very bad idea. Each user will get their own version!

If this is shared data (same for all users), consider moving it to the Application object.