Distributed caching is becoming popular with every passing day as it improves application performance and provides scalability. And with the need for a fast and scalable method, people are caching more and more data into the cache. The greater the application’s performance, the greater the number of client applications is going to be. This all seems like a win-win situation but when you consider how multiple clients place and fetch data to and from the data source, there’s a high probability that your database and the cache go out of sync.

To solve this problem, most people use a data expiration technique called time to live (TTL) or Absolute Expiration. This expiration automatically removes the specified cache item after the specified time. Although it is popular, this mechanism is nothing but an educated guess on how long the data is going to stay unchanged in the cache. If you feel confident that your data is not likely to change in this time duration or your data is not very sensitive, meaning you can afford to read old stale data from the cache then you can use expiration.

But if your data is business sensitive where you cannot afford to read inaccurate data from the cache then stale data is going to cost you a lot. In this situation, accessing out of date data is a risk you shouldn’t be taking.

Synchronizing NCache with SQL Server

To avoid the above-stated situation arising in your .NET application, you need to make sure that your cache stays in-sync with the database at all times. And for this exact purpose, NCache provides you a database synchronization feature called SQL dependency that lets you keep your cache synchronized with the SQL Server database.

Sql Dependency uses event notifications to keep your cache in sync with your database. Notification is a feature of SQL Server that notifies all database clients when a dataset changes in the database. NCache Server uses this feature to become an SQL Server client and to register itself with SQL Server through Sql Dependency. This way, SQL Server notifies NCache when specific data changes in the database. And, NCache keeps a mapping of cached items and the corresponding datasets of the database. And, with this, NCache can invalidate the cached item so a fresh copy is fetched from the database next time the application needs it.

NCache Details SQL Dependency

Figure: Synchronize Cache with SQL Server

NCache provides you Sql Dependency API through its Client API. And, all you need to do is provide the following parameters through this API when you’re adding cached items to NCache.

Connection string: For NCache to establish a connection with the database.
Query: For NCache to register notifications against a SELECT query.

NCache Details Notification Based Dependency

Sync NCache with SQL Server: A Quick Example

Let’s say that I have a .NET based online shopping application with a vast variety of items being searched by hundreds of clients at a time. The shopping portal opens and the first client buys the last two LED TVs that were available in the store. The application server that the client was connected to removes that item from the database while the cache is still unaware of this change.

Here, what you need to know is that if other clients access that item from the cache, the older copy of this item still remains available. And when they order each of those TV sets and pay for them, they will get a confirmation message even though these items are no longer in stock. And, this results in customers getting frustrated and unhappy with my business.

But if I use the NCache database synchronization feature with SQL server then my application does not face this data integrity problem as the cache always has correct data. This prevents the obvious loss that I was facing before.

I use the following code to add data into the cache using SQL dependency provided by NCache with the SqlCacheDependency method.

// Creating a connection string to establish a connection with the database

string connectionString = ConfigurationManager.AppSettings["connectionstring"];

// Creating the query which selects the data on which the cache data is dependent

string query = "SELECT ProductID, Quantity, Price FROM dbo.Products WHERE ProductID = 'A1Dell';";

// Creating SQL Dependency

var sqlDependency = new SqlCacheDependency(connectionString, query);

// Get product information against given product ID

var product = FetchProductByID("A1Dell");

// Generate a unique cache key for this product

string key = "Product#" + product.ProductID;

// Create a new cacheitem and add sql dependency to it

CacheItem item = new CacheItem(product);

item.Dependency = sqlDependency;

//Add cache item in the cache with SQL Dependency

cache.Insert(key, item);

After an item (in this case a product) is inserted in the cache with a dependency flag, any change in the data source item is a clue for the cache to remove the corresponding cache item from the cache.

The reason why you should use the NCache SqlCacheDependency method to synchronize data with the SQL server is simple. Your cache never becomes stale and you can confidently put all sorts of data and fetch it without facing data integrity problems.

NCache Details Read-Through Stored Procedures

Auto-Reload Cache Item through ReadThru Provider

Instead of invalidating a cached item, if you want the cache to automatically reload it when its corresponding data changes in the database, then you need to implement the IReadThruProvider interface in NCache. Now, whenever NCache receives an update notification from the SQL Server, instead of removing that item from the cache, NCache calls your ReadThru provider to fetch the updated data from the database.

You add the following line of code before inserting data into the cache and NCache handles auto-synching of the data for you.

// Create a new cache item and add sql dependency to it

CacheItem item = new CacheItem(product);

item.Dependency = sqlDependency;

// Resync if enabled, will automatically resync cache with SQL server

item.ResyncOptions = new ResyncOptions(true);

//Add cache item in the cache with SQL Dependency and Resync option enabled

cache.Insert(key, item);

Parametrized Queries in SQL Dependencies

Too many changes in the database require frequent invalidation of cached items. For each data invalidation, a SELECT query needs to be provided. And, this means that SQL Server needs to compile this SQL query before executing it. And, this causes performance degradation because query compilation takes a very long time.

To address this performance issue, NCache allows you to use parameterized SQL queries where you specify parameter values at runtime and these values change upon each execution. Here, the SQL query itself gets compiled upon first execution and no longer needs recompilation and this saves a lot of time and improves performance.

To use parameterized queries in your application, instead of writing a normal select query, you implement the following query:

// Creating the query which selects the data on which the cache data is dependent

// This query takes a parameter value at runtime and adds SQL Cache dependency to it

string query = "Select ProductID, Quantity, Price from dbo.Products where ProductID = @ProductID ";

// Creating and populating the parameter

SqlCmdParams param = new SqlCmdParams();

param.TypeName = "@ProductID";

param.Type = CmdParamsType.VarChar;

param.Value = "A1Dell";

// Adding the populated parameter to a dictionary

var sqlParam = new Dictionary<string, SqlCmdParams>();

sqlParam.Add(param.TypeName, param);

// Creating SQL dependency using parameterized query

var sqlDependency = new SqlCacheDependency(conn_string, query, SqlCommandType.Text, sqlParam);

Stored Procedure Based Sql Dependency

Many organizations prefer to keep all their SQL queries used by their applications inside their database in the form of stored procedures. This improves performance because all these stored procedures are precompiled and run much faster than dynamic SQL queries. And, secondly, they can keep all the database access SQL queries in one place and not scattered inside each application that is then difficult to modify in case they wanted to update their SQL queries.

For these organizations, NCache supports Stored Procedure Based Sql Dependency where instead of specifying dynamic SQL query or even parameterized SQL query, you can specify a Stored Procedure call.

The following SQL query creates a procedure with ProductID as a parameter. This stored procedure can be used in your application to synchronize cache with the database.

create PROCEDURE SelectProduct @ProductID varchar(30)

select ProductID, Quantity, Price from dbo.Products

where ProductID = @ProductID

go;

To call this stored procedure in your .NET application, use the following code snippet.

// Creating and populating a parameter

SqlCmdParams param = new SqlCmdParams();

param.Type = CmdParamsType.VarChar;

param.Value = "A1Dell";

// Adding the populated parameter to a dictionary

Dictionary<string, SqlCmdParams> sqlParam = new Dictionary<string, SqlCmdParams>();

sqlParam.Add("@ProductID", param);

// Creating SQL dependency using Stored Procedure

sqlDependency = new SqlCacheDependency(conString, SelectProduct, SqlCommandType.StoredProcedure, sqlParam);

Conclusion

If you have an ASP.NET based application and you aren’t using NCache as its primary data source, then you need to start using it now.

NCache is an in-memory solution so it boosts your application’s speed by a noticeable factor.
NCache doesn’t need any client interference to auto rebalance data, making your application extremely flexible.
And, NCache allows you to add as many servers as you wish at runtime, providing you with the most scalable solution for your .NET application.

If you have an ASP.NET application but the database you are using doesn’t support notifications, NCache has provided yet another great solution for exactly such a scenario. The method is called polling based dependency. Go check it out!

NCache Details Edition Comparison NCache Download