With time, distributed caching is becoming popular 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. However, there’s a high probability that your database and the cache will go out of sync when multiple clients place and fetch data to and from the data source. To counter 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 an educated guess on how long the data stays unchanged in the cache.
If you feel confident that your data is not likely to change in this 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 and you cannot afford to read inaccurate data from the cache, then stale data will cost you a lot. In this situation, accessing out-of-date data is a risk you shouldn’t take.
NCache Details Data Expiration Types NCache Docs
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 with a database synchronization feature called SQL dependency that lets you keep your cache synchronized with the SQL Server database. SQL Dependency uses Query notifications to synchronize your cache with the database if any change occurs in the datasets.
NCache Details SQL Dependency NCache Docs
Query Notifications in SQL Server
Query Notifications are 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. With this, NCache can invalidate the cached item, so a fresh copy is fetched from the database the next time the application needs it.
NCache provides you SQL Dependency API through its Client API. However, you must provide the following parameters through this API when adding cached items to NCache.
- Connection string: to establish a connection with the database.
- CommandText: to determine the CommandText used by dependency.
NCache Details SQL Dependency Setup SQL Server Environment
Sync NCache with SQL Server: A Quick Example
Let’s say that you have a .NET-based online shopping system with a range of searchable items accessed by hundreds of clients simultaneously. The shopping portal opens on Christmas day with a 40% discount on the entire stock. The application server updates the unit price of the products in the database while the cache is unaware of this change.
If clients access items from the cache, the older copy of these items, with no discount, remains available. When customers search for products, they get the old unit prices Christmas – resulting in customers getting frustrated and unhappy with the business.
But if you use the NCache database synchronization feature with the SQL server, the user can enable notifications to keep the data synced with the database for every update. In this case, the application does not face this data integrity problem as the cache always has the correct data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
// Creating connection string to get connected with database. string connectionString = "Server=SERVERNAME; Database=NORTHWIND; UserId=USERID; Password=PASSWORD;"; // Getting products from database. List products = FetchProductFromDB(); // Creating dictionary of CacheItems. Dictionary<string, CacheItem> cacheItems = new Dictionary<string, CacheItem>(); foreach(Product product in products) { string productKey = $"Product: {product.ProductID}"; // Creating an SQL dependency on the unit price of product. Whenever the Unitprice changes, the product is removed from the cache. string query = $"Select UnitPrice from dbo.Products where ProductID = {product.ProductID}"; // Creating dependency on all the products. SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query); CacheItem productItem = new CacheItem(product); // Adding Dependency to product item productItem.Dependency = dependency; } // Adding CacheItems in cache cache.Add(productKey, productItem); |
The reason why you should use the NCache SqlCacheDependency method to synchronize data with the SQL server is simple. Your cache data never becomes stale, and you can confidently put all sorts of data and fetch it without facing data integrity problems.
NCache Details SqlCacheDependency NCache Docs
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 the auto-syncing of the data for you.
|
1 2 3 4 5 6 7 8 9 |
// 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); |
NCache Details Data Source Providers Read-Through NCache Docs
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. 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
// Creating Param to be passed in stored procedure dictionary. SqlCmdParams paramProductID = new SqlCmdParams { Type = CmdParamsType.Int, Value = product.ProductID }; // Creating stored procedure params. Dictionary<string, SqlCmdParams> parameter = new Dictionary<string, SqlCmdParams>(); parameters.Add("@ProductID", paramProductID); CacheItem productItem = new CacheItem(product); /*Creating an SQL dependency on the unit price of the product. Whenever the unit price changes, the product is removed from the cache*/ SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query, SqlCommandType.Text, parameters); // Adding Dependency to the product item productItem.Dependency = dependency; cache.Add(productKey, productItem); |
NCache Details SqlCacheDependency SqlCacheDependency
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. Secondly, they can keep all the database access SQL queries in one place and not scattered inside each application which is difficult to modify if they want 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 the cache with the database.
|
1 2 3 4 |
create PROCEDURE sp_GetUnitPriceByProductID @ProductID int as Select UnitPrice from dbo.Products where ProductID = @ProductID; |
To call this stored procedure in your .NET application, use the following code snippet.
|
1 2 3 4 5 6 7 8 9 10 11 |
// Specifying stored procedure, created in database. string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID"; /*Creating an SQL dependency on the unit price of product. Whenever the unitprice changes, the product is removed from the cache*/ SqlCacheDependency dependency = new SqlCacheDependency(connectionString, spGetUnitPriceByProductID, SqlCommandType.StoredProcedure, parameters); // Adding Dependency to product item productItem.Dependency = dependency; cache.Add(productKey, productItem); |
NCache Details Stored Procedures SQL Search
Conclusion
If you aren’t using NCache as a primary data source for an ASP.NET-based application, start using it now as:
- NCache is an in- memory solution that doesn’t need any client interference to auto-rebalance data, making your application extremely flexible.
- It 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 provides you with a method called polling-based dependency.
