Alachisoft.com

Synchronize NCache with Relational Databases

NCache is an extremely fast and scalable in-memory distributed cache that lets you improve your application performance and scalability. Unlike a relational database that quickly becomes a bottleneck, NCache scales linearly by letting you add more cache servers to increase transaction capacity.

But when you cache application data, you are creating a copy of data in the cache that also exists in your relational database. And, if this data in the database changes, you want to make sure the cache is also updated so it is always consistent with the database.

NCache provides powerful database synchronization features where the cache updates itself when data changes in the relational database. Each feature is geared toward a specific type of usage and collectively they handle a variety of scenarios for synchronization with the database. These features are:

  • SqlDependency: This allows you to synchronize the cache with SQL Server with the help of SQL Notifications.
  • OracleDependency: This allows you to synchronize the cache with Oracle with the help of Oracle Notifications.
  • DbDependency: This allows you to synchronize the cache with any OLEDB compliant database including DB2 through polling.
  • CLR Procedures: This allows you to have CLR procedures in SQL Server or Oracle which directly update the cache when data in the database changes.
  • Read-through handler for auto reload: This features allows NCache to automatically reload a fresh copy of the cached item instead of removing it when data in the database changes.

Using SqlDependency

SqlDependency is used by NCache in case your database is SQL Server 2008/2012.

You can specify a SqlDependency for any cached item when you’re adding or updating it in the cache. And, this SqlDependency is a SQL statement or a stored procedure call corresponding to a data-set in SQL Server database. SQL Server then monitors this data-set for any additions, updates, or removals and when that happens, SQL Server notifies NCache.

SqlDependency notifies NCache through SQL Notifications almost immediately after database update. And, these notifications are essentially .NET events.

Here is how you can use SqlDependency in your .NET code.


CacheItem item = newCacheItem(myObject);
item.Dependency = SqlDependencyFactory.CreateSqlYukonDependency(connectionString,"SELECT CustomerID FROM Customers WHERE CustomerID = " + custId);
_cache.Insert (key, item);

Using OracleDependency

OracleDependency is used by NCache in case your database is Oracle 10g or later and running on either Windows or Unix.

Just like SqlDependency, you can specify a OracleDependency for any cached item when you’re adding or updating it in the cache. And, this OracleDependency is a SQL statement or a stored procedure call corresponding to a data-set in Oracle database. Oracle Server then monitors this data-set for any additions, updates, or removals and when that happens, Oracle Server notifies NCache.

OracleDependency notifies NCache through Oracle Notifications almost immediately after database update. And, these notifications are using Oracle’s client/server communication underneath and then talking to NCache through .NET.

Here is how you can use OracleDependency in your .NET code.


CacheDependency orclSync = new OracleCacheDependency (connectionString, "SELECT CustomerID FROM Customers WHERE CustomerID = 006");
_cache.Insert("Customer:David:006", "my Item", orclSync, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);

Using DbDependency

DbDependency is used by NCache in case your database is neither SQL Server nor Oracle but is an OLEDB compliant database.  You can also use DbDependency with SQL Server and Oracle if you do not want their event notifications which could get chatty in case your data is changing very rapidly.

In DbDependency, you are asked to create a special table in the database called ncache_db_sync which contains one row for each cached item with DbDependency. And, you have to modify your database triggers to update rows in this table when the corresponding data in the database changes. Then, NCache polls this ncache_db_sync table looking for rows that were updated. And, in one poll, NCache can fetch thousands of rows and then synchronize them with the database. Synchronization here means removing the corresponding cached item from the cache.

Here is how you can use DbDependency in your .NET code.


DBCacheDependency dependency = DBDependencyFactory.CreateOleDbCacheDependency(connectionString, "Primary Key:dbo.Customers");
_cache.Insert(Customer.CustomerID + ":dbo.Customer",Customer, dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, Alachisoft.NCache.Runtime.CacheItemPriority.Default);

Using CLR Procedures

There are times when you want to use CLR Procedures instead of either SqlDependency, OracleDependency, or DbDependency. This is when you have a very large number of items (e.g. hundreds of thousands or even millions) in the cache and all of them need to be synchronized with the database. In such situations, SqlDependency, OracleDependency, and DbDependency become too heavy.

In such situations, it is much better to write a CLR Procedure in SQL Server or Oracle on Windows. And, then call this CLR Procedure from your database triggers when the relevant data changes. This CLR Procedure then makes NCache API calls to either add, update, or remove the corresponding cached item from the cache. And, to achieve best performance, this CLR Procedure should make async NCache API calls.

Here is an example of a CLR Procedure:


public partial classStoredProcedures
	{
	Cache _cache = NCache.InitializeCache("myDemoCache");
	[SqlProcedure()] 
    	public static void UpdateCustomerCache(String customerId)
		{
    		// fetch the customer row from Customers table
    		// ...
    		// update the cache with a new Customer object
    		_cache.InsertAsync("key", customerId);
		}
	}
    

Using Read Through Handler for Auto Reload

The default behavior of database synchronization is to remove the corresponding cached item from the database when the corresponding data in the database changes. However, there are situations where you simply want to update it with the latest version of the data.

To handle this need, NCache allows you to combine database synchronization (SqlDependency, OracleDependency, or DbDependency) with Read-through hander feature of NCache. With this, NCache simply calls the Read-through handler to reload the latest copy of the cached item and then updates the cache with it.


What to Do Next?