The Query Result Cache

Quite some time ago we touched upon the details of the caches maintained by Telerik OpenAccess ORM in the these 2 blog posts – First Level Cache, Second Level Cache.  In this post we will provide some more information about the ‘Query result cache’ which is held as a part of the 2nd Level Cache.

What is the Query Results Cache ?

When you execute a LINQ query to fetch some objects from the database there are quite a few things that take place in the background before you actually get back the result. Let’s list some basic actions that are taken -

1. Analysing the LINQ query and converting into an internal command tree.
2. Generating an appropriate SQL query taking into account various things like the mapping and current FetchStrategy.
3. Materializing the raw results into concrete instances requested in the query.

The above steps provide a very simplified description of the actual work being done. The process of translating the LINQ query to a corresponding SQL query accounts for a considerable amount of time in the entire process. This ‘compiled query’ is cached for future use. If the exact same query is executed again with same parameter values and fetch strategy, the LINQ to SQL translation is avoided but the SQL query is nevertheless executed again.  This is where the Query Result Cache (QRC) comes into the picture.

When the query is executed the very first time, OpenAccess will cache the ‘compiled query’ along with  parameter information and all the data that was retrieved by the query, in the ‘Query Results Cache’.  On subsequent invocations of the query the relevant information is retrieved from the cache and returned as the result, thus avoiding an unnecessary query to the database.

Let’s look at an example. Consider the following simple LINQ query that queries for all Products,from the Northwind database,  with more than a specified unit price.

   1: var prods = from p in context.Products

   2: where p.UnitPrice > maxPrice

   3:             select p;

   1: SELECT a.[ProductID] AS COL1, a.[CategoryID] AS COL2, a.[Discontinued] AS COL3, a.[ProductName] AS COL4, 

   2: a.[QuantityPerUnit] AS COL5, a.[ReorderLevel] AS COL6, a.[SupplierID] AS COL7, a.[UnitPrice] AS COL8,

   3: a.[UnitsInStock] AS COL9, a.[UnitsOnOrder] AS COL10 FROM [Products] a WHERE

   4: a.[UnitPrice] > @p0 ORDER BY a.[ProductID] 

This compiled information is cached along with the object key of each object in the result. The cache would look as follows

One of the first questions that comes to mind is what if the query would fetch different results on subsequent calls? This can happen if your application has modified related data or another application has modified it. In the first case, the moment you commit your changes, all the queries that include a type whose data has been modified will be cleared i.e the compiled query and it’s results will be removed from the cache. Hence the next time you execute the LINQ query OpenAccess will fire a database query thereby ensuring that the results are fetched afresh. If another application modifies data you can disable caching for types that you know will be modified externally or you can manually evict instances of such types.

Note that the 2nd level cache is used only for non-transactional reads and reads in optimistic transactions. It is bypassed for pessimistic transactions to ensure that database locks are obtained. Only data read in an optimistic transaction or outside of a transaction is cached.

How to configure the cache?

The Query Results cache is part of the 2nd Level Cache and the 2nd Level Cache is disabled by default. You can enable the 2nd Level Cache using the ‘Model settings’ dialog. (right-click on the designer surface and select ‘Show Model Settings’). The ‘Backend settings’ tab has a ‘Second level cache’ tab where the cache can be configured. Here you can specify the maximum number of objects and query results to be held in the cache.  If you would like to control caching for individual types this can be done by selecting a class on the designer surface,pressing F4 to view it’s properties and modifying the ‘Cache Policy’ for the class. In case you specify ‘NoCache’ as the policy instances of the type and query results involving that type will not be cached at all.

Queries that return more than 500 rows are not cached by default. This limit can be configured specifying the ‘SecondLevelCache.NumberOfObjectsPerQueryResults’ property on the BackendConfiguration instance of the context. This advanced property needs to be set via code.

Thus the Query Results cache helps improve performance by avoiding repetitive query compilation and  unnecessary database queries. In one of our next blog post we will see the various steps involved in obtaining data from the server when a simple LINQ query is executed, how the query is compiled and executed , caches are checked and populated and objects are materialized.