LLBLGen Pro v4.0 feature highlights: Resultset Caching

This is the second post of a series about the new features in LLBLGen Pro v4.0, which was released on April 6th, 2013. Today I'd like to highlight a new major feature of the LLBLGen Pro Runtime Framework, our own ORM framework shipped with LLBLGen Pro, namely Resultset Caching.

I have written about caching in ORMs before, though that particular way of caching was about materialized objects, which is something else than Resultset Caching. Resultset Caching is a mechanism which caches the actual resultset used during the execution of a select query for a given duration. This means that if a given Linq / QuerySpec query is specified to have its resultset cached for e.g. 10 seconds, every execution of the exact same query with the same parameters will pull the results from the cache for the 10 seconds after the query was first executed on the database.

Caching can lead to better performance in situations where a query is taking a relatively long time in the database and/or is executed a lot of times: by automatically re-using already read rows from the cache instead of from the database, a round trip + query execution is omitted. This is completely transparent and automatic: the only thing the developer has to do is to specify with a query whether it should cache its resultset and for how long.

The caching system is opt-in per query, so by default, all queries are executed on the database, every time, and no caching takes place. This is ideal because caching resultsets means that the resultset of a query which is defined to cache its results might pull its resultset from the cache instead of the database and therefore it might be the data is out-of-date with the data in the database: the cache data is 'stale' data, but for many situations this is ok, at least for a short period of time.

Resultset caching is done through cache objects which implement the caching of resultsets, either by delegating it to 3rd party servers like the .NET system cache or e.g. Memcached, or by doing the caching themselves like the built-in ResultsetCache class. Cache objects implement a common interface, IResultsetCache , and it's easy to implement a cache object class yourself. With the LLBLGen Pro Runtime Framework we ship the built-in ResultsetCache class and in the recently started LLBLGen Pro contrib library (github) we ship an implementation of the IResultsetCache interface for caching data in the .NET 4+ cache MemoryCache. As you can see with the MemoryCache supporting implementation, it's very easy to add a class which caches the data in your cache system of choice, e.g. Redis.

Registering a cache

Before caching can take place, at least one cache has to be registered with the CacheController. The CacheController is a static class which keeps track of the registered caches and is consulted by the query object whether a cached set of rows is available for that query.

Example of registering an instance of the ResultsetCache:

After this line, all queries executed over a connection with the same connection string and which are specified to have their resultsets cached, are cached in the ResultsetCache instance registered. This means you can have multiple caches if you have multiple databases in use at the same time. You can also use one cache for all databases in use, by specifying an empty string as the connection string.

What makes a query unique? The Cache Key

Resultsets are cached in a cache under a key, a CacheKey instance. A CacheKey is created from a query object which provides the values for the key.

A CacheKey uses:

• The SQL statement
• All parameter values before execution
• Flags for in-memory distinct/limiting/paging
• Values for rows to skip/take in in-memory paging scenarios

A hashcode is calculated from these values and an Equals implementation makes sure two CacheKey instances are only considered equal if all of these values are indeed equal. This makes sure that two queries which differ only in e.g. parameter values will not return each other's resultsets from the cache. Two pieces of code which are fetching data differently (e.g. a normal entity collection fetch and a projection using all entity fields onto a custom class) but result in the same SQL query and parameters will re-use the same set from the cache, if both queries are defined to be cacheable.

Specifying a query to have its resultset cached

LLBLGen Pro has three ways to specify a query: Linq, QuerySpec and the Low-level API. We primarily added caching specification code to Linq and QuerySpec, as those two query APIs are the recommended way to formulate queries. I'll show how to specify caching directives in Linq and QuerySpec below.

QuerySpec

// typed list fetch 
var tl = new OrderCustomerTypedList();
var q = tl.GetQuerySpecQuery(new QueryFactory())
                 .CacheResultset(10);  // cache for 10 seconds
new DataAccessAdapter().FetchAsDataTable(q, tl);

// typed view fetch
var qf = new QueryFactory();
var q = qf.Create()
            .Select(InvoicesFields.CustomerId, InvoicesFields.CustomerName, InvoicesFields.OrderId)
            .Where(InvoicesFields.Country.StartsWith("U"))
            .CacheResultset(10);        // cache for 10 seconds
var tv = new InvoicesTypedView();
new DataAccessAdapter().FetchAsDataTable(q, tv);

// dynamic list / custom projection
var qf = new QueryFactory();
var q = qf.Employee
        .From(QueryTarget.InnerJoin(qf.Order)
                   .On(EmployeeFields.EmployeeId == OrderFields.EmployeeId))
        .OrderBy(EmployeeFields.EmployeeId.Ascending()).Offset(1).Distinct()
        .Select(() => new
            {
                EmployeeId = EmployeeFields.EmployeeId.ToValue<int>(),
                Notes = EmployeeFields.Notes.ToValue<string>()
            })
        .CacheResultset(5);    // cache for 5 seconds
var results = new DataAccessAdapter().FetchQuery(q);

// custom projection with nested set
var qf = new QueryFactory();
var q = qf.Create()
        .Select(() => new
          {
                Key = CustomerFields.Country.ToValue<string>(),
                CustomersInCountry = qf.Customer.TargetAs("C")
                                         .CorrelatedOver(CustomerFields.Country.Source("C") ==
                                                         CustomerFields.Country)
                                         .ToResultset()
          })
        .GroupBy(CustomerFields.Country)
        .CacheResultset(10);     // cache all for 10 seconds
var results = new DataAccessAdapter().FetchQuery(q);

// entity fetch
var qf = new QueryFactory();
var q = qf.Customer
            .Where(CustomerFields.Country == "Germany")
                 .WithPath(CustomerEntity.PrefetchPathOrders
                               .WithSubPath(OrderEntity.PrefetchPathEmployee),
                                      CustomerEntity.PrefetchPathEmployeeCollectionViaOrder)
            .CacheResultset(10);    // cache all for 10 seconds
var customers = new DataAccessAdapter().FetchQuery(q);

Linq

// custom projection, with nested set
var metaData = new LinqMetaData(new DataAccessAdapter());
var q = (from c in metaData.Customer
          group c by c.Country into g
          select new
          {
             g.Key,
             CustomersInCountry = (
                  from c2 in metaData.Customer
                  where g.Key == c2.Country
                  select c2)
          })
         .CacheResultset(10);    // cache all for 10 seconds

// entity fetch with prefetch path
LinqMetaData metaData = new LinqMetaData(adapter);
var q = (from c in metaData.Customer
          where c.Country == "Germany"
          select c).WithPath<CustomerEntity>(cpath => cpath
                 .Prefetch<OrderEntity>(c => c.Orders)
                     .SubPath(opath => opath
                         .Prefetch(o => o.OrderDetails)
                         .Prefetch<EmployeeEntity>(o => o.Employee).Exclude(e => e.Photo, e => e.Notes)))
          .CacheResultset(10);    // cache all for 10 seconds

As you can see, it's just an operator to the query, and from then on its resultset is cached in the cache object registered for the connection string used at runtime. If there's already a resultset, that one is used; if there's no cached resultset found, the one read from the database is cached for the specified amount of time so the next execution of the same query will re-use that resultset.

an easy way to tweak your data-access code on a per-query basis.