Cache Synchronize with SQL Server Database

With the increasing demand for high-performance and scalable applications, distributed caching has become a necessity. It enhances application speed and responsiveness. But when multiple clients access and modify data concurrently, the cache and database lose synchronization. This is typically addressed through expiration mechanisms like Time-to-Live (TTL) or Absolute Expiration which removes cache items after a certain time. While this mechanism helps, it’s based on the assumption for how long the data remains unchanged, leading to the possibility of stale data.

For data that doesn’t change often or is not critical, expiration-based caching might be an option. In contrast, for data sensitive to business operations where outdated information can cause financial or operational issues, stale cache data is a big risk. In these cases, an effective cache synchronization mechanism is required.

Synchronizing NCache with SQL Server

To keep the cache synchronized with the database, NCache provides a feature called SQL Dependency. This uses SQL Server Query Notifications to invalidate cache items when their corresponding database records change to avoid any outdated data.

Query Notifications in SQL Server

Query Notifications is a SQL Server feature that alerts registered clients whenever specific dataset changes occur. NCache Server registers itself as an SQL Server client using SQL Dependency, allowing automatic synchronization between the cache and database. It works in the following sequence:

SQL Server detects changes in the subscribed data.
It sends a notification to NCache.
NCache invalidates the outdated cached item.
The next time the application requests that data, it retrieves a fresh copy from the database.

Figure: Synchronize Cache with SQL Server

Implementing SQL Dependency in .NET 8 with NCache

NCache provides a SQL Dependency API to register cache items for automatic invalidation. To enable this feature, you need to pass the following parameters when adding data:

Connection String: Defines the SQL Server connection.
CommandText: Specifies the SQL query being monitored.

Example: Synchronizing Cache with SQL Server in .NET 8

Consider a .NET 8-based online shopping platform where thousands of customers browse products simultaneously. On Christmas Day, a 40% discount is applied across the entire catalog. If the application cache does not reflect this update, customers might see incorrect prices, causing frustration.

With NCache SQL Dependency, the cache remains synchronized with the database, ensuring accurate pricing information.

// Define the connection string to SQL Server

string connectionString = "Server=SERVERNAME; Database=NORTHWIND; User Id=USERID; Password=PASSWORD;";

// Fetch products from the database

List products = FetchProductsFromDB();

// Create a dictionary to store cache items

Dictionary<string, CacheItem> cacheItems = new();

foreach (Product product in products)

{

string productKey = $"Product:{product.ProductID}";

string query = $"SELECT UnitPrice FROM dbo.Products WHERE ProductID = {product.ProductID}";

// Create SQL Dependency

SqlCacheDependency dependency = new(connectionString, query);

CacheItem productItem = new(product) { Dependency = dependency };

cacheItems[productKey] = productItem;

}

// Add products to NCache

cache.AddBulk(cacheItems);

Auto-Reload Cache with ReadThru Provider

To maintain up-to-date entries following a database update, consider utilizing NCache’s ReadThru Provider to automatically refresh the cache. This approach guarantees that the cache always contains the latest data, eliminating the need for the application to manually retrieve it.

To enable auto-reloading, modify your caching logic as follows:

// Create a new cache item with SQL dependency

CacheItem item = new(product);

item.Dependency = sqlDependency;

// Enable automatic cache resynchronization

item.ResyncOptions = new ResyncOptions(true);

// Add the cache item with Resync enabled

cache.Insert(key, item);

Optimizing Cache Synchronization with Parameterized Queries

Frequent database updates can cause cache invalidations which impacts performance. SQL Server 2022 has optimizations for parameterized queries, which reduces query compilation overhead. NCache leverages these optimizations by allowing precompiled queries with parameters, improving efficiency.

Example: Using Parameterized Queries

// Define SQL parameters

SqlCmdParams paramProductID = new() { Type = CmdParamsType.Int, Value = product.ProductID };

Dictionary<string, SqlCmdParams> parameters = new() { { "@ProductID", paramProductID } };

// Create SQL Dependency with parameterized query

SqlCacheDependency dependency = new(connectionString, query, SqlCommandType.Text, parameters);

CacheItem productItem = new(product) { Dependency = dependency };

cache.Add(productKey, productItem);

Stored Procedure-Based SQL Dependency

Many enterprises prefer using stored procedures for database interactions due to their efficiency and maintainability. NCache supports Stored Procedure-Based SQL Dependency, allowing organizations to register cache synchronization based on stored procedure execution.

Example: Creating a Stored Procedure for SQL Dependency

CREATE PROCEDURE sp_GetUnitPriceByProductID @ProductID INT

SELECT UnitPrice FROM dbo.Products WHERE ProductID = @ProductID;

Example: Using Stored Procedure in .NET 8

// Specify the stored procedure name

string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";

// Create SQL Dependency with Stored Procedure

SqlCacheDependency dependency = new(connectionString, spGetUnitPriceByProductID, SqlCommandType.StoredProcedure, parameters);

CacheItem productItem = new(product) { Dependency = dependency };

cache.Add(productKey, productItem);

Conclusion

If you are building high-performance ASP.NET 8 applications, synchronizing the cache with SQL Server using NCache is a robust solution. Here’s why:

NCache is an in-memory solution that ensures ultra-fast data access.
No client interference is needed for auto-rebalancing, enhancing scalability.
Seamless integration with SQL Server notifications and stored procedures.
Eliminates stale data issues, ensuring data consistency across the application.
Highly scalable as it adds more servers dynamically to handle increasing loads.

For applications where SQL Server does not support Query Notifications, NCache provides Polling-Based Dependency, ensuring real-time synchronization even in such environments.

Start using NCache SQL Dependency today to eliminate data integrity issues and ensure your application always serves up-to-date information.