subsonic with support for caching

Question

Having a project with following requirements in mind.

• data reading intensive application.
• 100 max concurrent users a times. Application have very high traffic
• Though data is huge it is getting modified only once a day

Decided to use subsonic cause of ease of development and potential to work in high traffic environment.

Though few things are not yet found/solved to work with SubSonic 3

• Which type of layer to use Active Records, Repository, Linq To SQL
• working with paging / sorting stored procedures (cause they will give better performance over inbuilt paging mechanism, when displaying 10000+ rows with paging and sorting. right?? )
• Caching, with project requirement it is quite clear, heavy use of caching is required. But could not find suitable solution, which will work with subsonic. do I have to create separate layer for it and if yes, a short example would be helpful.

Answer 1

I wrote a CacheUtil class for subsonic 2.x ActiveRecord. It's based on some code someone posted on the old subsonic forums. (This is from a forum that was deleted before the last forum was removed. This is why software forums should be permanent.) Here is an example of a cache Find method. You could adapt it to ss3. There are also inserts, fetchall, delete, clear, etc. Rob Connery said at the time that caching was problematic, and it was left out of ss2 on purpose. By using HttpRuntime.Cache I share the cache between a web application and service simultaneously. I believe I can do this since it's a small application, always on a single server.

public static RecordBase<T> Find<T, ListType>(object primaryKeyValue)
    where T: RecordBase<T>, new()
    where ListType: AbstractList<T, ListType>, new()
{
    string key = typeof(T).ToString();
    if(HttpRuntime.Cache[key] == null)
        FetchAll<T, ListType>();
    if(HttpRuntime.Cache[key] != null)
    {
        ListType collection = (ListType)HttpRuntime.Cache[key];
        foreach(T item in collection)
        {
            if(item.GetPrimaryKeyValue().Equals(primaryKeyValue))
                return item;
        }
    }
    return null;
}

Answer 2

I wrote a post about how I used caching with SubSonic 2.x. It isn't 100% compatible with 3.x but the concepts are the same.

Answer 3

I answered this similarly over here Thread-safe cache libraries for .NET. Basically you need a CollectionCacheManager - I then add a layer on top for each type and funnel all requests through this individual cache controllers, which in turn are using the 1 collectioncachecontroller. At the outer layer I mix pure subsonic, linq, whatever fits the bill at the time. That's the beauty of SubSonic is that it should not get in your way. As far as stored proc performance I would point to Jeff Atwoods articles over at CodingHorror and reevaulaute your savings. Hardware is dirt cheap, as is memory, databases are not. Personally I keep the database super simple and lightweight, and prefer to let my webserver cache everything in memory. The database server gets to do very little work which is the way I like it. Adding a few extra load balanced web servers isn't nearly as big of a deal as increasing database throughput, clustering, or sharding a a DB. SQL & Stored Procs can also be ridiculously difficult to write, and maintain. Take that budget that you would have spent on your time doing that, and instead beef up your hardware... Remember hardware is dirt cheap, good developers are not. Good luck!