Skip to main content

Cached Data Store

  • 6 minutes to read

XPO provides functionality for a cache at the data store level. The cache stores queries and their results as they are being executed on a data store. Whenever a query, which has been executed before, passes the cache, the result from that query is returned back immediately without a roundtrip to a data store. This significantly improves performance in general and ensures that as little data as possible is transferred over the wire in distributed applications.

To enable data store caching, four classes specifying the Root and its Nodes - DataCacheRoot, MSSql2005SqlDependencyCacheRoot, DataCacheNode and DataCacheNodeLocal - must be combined. The minimum setup of the cache requires one Root (DataCacheRoot or MSSql2005SqlDependencyCacheRoot) and one Node (DataCacheNode or DataCacheNodeLocal). It is possible to build cache hierarchies out of a single Root and any number of Nodes, which can be linked to the Root or another Node. This makes sense in client/server setups, when certain parts of an application need to use different settings for their data access, such as current data.

The Nodes actually cache data, while the Root stores the information about table updates and synchronizes it with Nodes. To keep the table update information and cached data in sync, the Root and Nodes communicate with each other using the ICacheToCacheCommunicationCore channel. Every time a Node contacts its parent (a Root or another Node), table update information is passed in the direction from Root to Node. These regular contacts between a Node and its parent are required to keep the table information current. The latency for these contacts can be specified using the DataCacheNode.MaxCacheLatency field. This field defines the maximum time that is allowed to pass before a contact to the parent becomes mandatory. So, if a Node receives a query, it first finds a cached result set for this query, and if more time than specified by DataCacheNode.MaxCacheLatency has passed since its last parent contact, it will perform a quick request to its parent to synchronize table update information.


Data store caching relies on the idea that the cache structure knows about changes being made to data. So, even in a multi-user setup, you should make sure that all queries and updates are performed in a way that allows them to be recognized by the cache structure. To accomplish this, all client requests for data have to be routed through the client-side Nodes (DataCacheNode instances) and server-side Root (or server-side Root-Node chains). If, for any reason, there are changes in the database that have been made without going through the cache structure, you can use the following utility methods.

Direct SQL queries and stored procedure calls are not cached. To properly adjust a cache after calls, use any of the methods listed above. If you are using a Microsoft SQL Server (version 2005 and later) database as a backend for a data store, you can enable the cache hierarchy to be automatically notified about table updates using SqlDependency (see below).

Using SqlDependency

SqlDependency is a Microsoft SQL Server feature (found in version 2005 and later) that allows the database server to notify a client about changes that occur in the database. You can enable the cache hierarchy to exploit this feature to be automatically notified about any changes made to a cached database (even if they are made outside the cache hierarchy). To accomplish this, do one of the following.

string connectionString = "data source=.;initial catalog=DBName;integrated security=sspi";
IDisposable[] disposeOnDisconnect;
IDataStore node = MSSql2005SqlDependencyCacheRoot.CreateProviderFromString_WithCache(
    connectionString, AutoCreateOption.DatabaseAndSchema, out disposeOnDisconnect);
XPDictionary dict = new ReflectionDictionary();
XpoDefault.DataLayer = new ThreadSafeDataLayer(dict, node);

The SqlDependency feature is resource-demanding - every time a change happens in a database table that is being monitored, every client subscribed to SqlDependency must be notified. It is thus recommended to have only one MSSql2005SqlDependencyCacheRoot associated with a database, publishing change notifications to all other cache hierarchy Nodes.

To learn about prerequisites for the SqlDependency feature use, refer to the Special Considerations When Using Query Notifications MSDN article.

Cache Configuration Settings

With cache configuration settings, you can easily configure the caching scope for Nodes by designating tables to be cached. Since there is no need to cache tables that are frequently changed, you can exclude them from the caching scope using configuration settings. To specify the settings, use the Root’s DataCacheBase.Configure method. For the MSSql2005SqlDependencyCacheRoot, use the corresponding MSSql2005SqlDependencyCacheRoot.CreateSqlDependencyCacheRoot overloaded method.

Connecting to Cached Data Stores in Distributed Applications

You can publish cached data stores (ICachedDataStore implementers) in your distributed applications. When your client application connects to a cached data store using either the http://host:port/servicename.svc or net.tcp://host:port/servicename connection string format, XPO automatically creates a Node (a DataCacheNode object) on the client. You can then create any number of Nodes and Node chains on the client, and link them to this Node to create a cache hierarchy. If you exploit the SqlDependency feature in your cached data store, the resulting distributed application setup will benefit from change notifications on the database server without great resource requirements.

On the service side, we recommend creating the DataCacheRoot only. Normally, a service that utilizes the CachedDataStoreService class should be published on the same machine or in the same local network with the database server. In this configuration, traffic between the service and the database should not significantly affect performance, and usually it is not necessary to cache these queries. However, some applications may perform many heavy queries with aggregations or grouping, and these operations may take significant time on the database side. We provide a DataCacheNode descendant for this situation - DataCacheNodeLocal. If your application performs many select operations with grouping and aggregations, check if using this class in the service application benefits performance.



You can try the functionality described here in the Connecting to a Data Store | Data Caching section of the XPO Tutorials demo (C:\Users\Public\Documents\DevExpress Demos 22.2\Components\WinForms\Bin\XpoTutorials.exe).