Databases are an integral part of most .NET enterprise applications, enabling efficient data retrieval and manipulation. in high-transaction environments, relying on databases might lead to performance degradation. It does not help matters that the databases used these applications are often difficult to scale. To solve this problem, using a distributed caching solution, such as NCache helps mitigate unnecessary and expensive trips to the database.
How to Cache Database?
Caching a database is simpler than it seems by introducing a caching layer, such as NCache, between your application and database, you ensure that frequently accessed data is quickly retrieved from the cache, reducing load on the database. This blog details the four most common caching patterns that can help optimize your application’s performance.
1. Object Caching
This strategy suggests that if your data does not exist in the cache, you can fetch it from the database and then insert it into the cache, such as NCache. Successive requests are served directly from the cache. For issues regarding stale data, you can add expiration to the cache item as metadata so that it can be removed from the cache and then reinserted from the database when called next. For instance, the following code fetches an instance of Customer from the cache, if it exists and updates it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
string customerKey = $"Customer:ALFKI"; Customer customer = FetchCustomerFromDB("ALFKI"); // Update Customer's Phone customer.Phone = "0921-12 34 65"; // Create CacheItem CacheItem cacheItem = new CacheItem(customer); cacheItem.Expiration = new Expiration(ExpirationType.Sliding, TimeSpan.FromMinutes(5)); // Update cutsomer in DB and Cache if (UpdateDB(customer)) { cache.Insert(customerKey, cacheItem); } |
2. Cache Reference Data and Use SQL Queries to Search it
Static or reference data refers to data that is only read and rarely changed. Therefore, caches (like NCache) work best when used for reference data. Thus, users are advised to cache the entirety of their reference data and then use SQL queries to search the cache for it rather than querying the database. However, please note that if you don’t cache the entire reference dataset (e.g., all the products), then your SQL queries against the cache will return invalid results. If you’ve already preloaded the cache with all the reference data, the following example guides you how to search for it effectively:
|
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 |
// Items are already present in the cache // Create a query which will be executed on the dataset // Use the Fully Qualified Name (FQN) of your own custom class string query = "SELECT * FROM FQN.Product WHERE ProductID > ?"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); // Providing parameters for query queryCommand.Parameters.Add("ProductID",50000); // Executing QueryCommand through ICacheReader ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand, false, 0); // Check if the result set is not empty if (reader.FieldCount > 0) { while (reader.Read()) { string result = reader.GetValue(0); // Perform operations using the retrieved keys } } else { // Null query result set retrieved } |
3. Handling One-to-Many Relations in the Cache
Another important thing to consider is that most datasets do not exist in isolation, and are often related to other datasets. For example, if we consider the Northwind database (used by Microsoft), the ‘Orders’ table is related to the ‘Customers’ table. These entities are considered related entities in the domain of a .NET application. Unfortunately, with most caches the data has no relational model in the cache and exists only as a key-value pair. Luckily, using a cache like NCache, you can recreate these relationships within the cache as well.
The following example adds a CacheItem order to the cache using the Insert method, which is dependent on the customer in the cache. This means as soon as the customer is updated or deleted, the order of the customer is already deleted from the cache.
|
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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
// Get customer from database. Customer customer = FetchCustomerFromDB(); // Add customer in cache cache.Add(customer.CustomerID, customer); // Get order from customer. Order order = new Order { OrderID = 10248, OrderDate = DateTime.Now, ShipAddress = "Carrera 22 con Ave. Carlos Soublette #8-35" }; // Save order to database. bool isOrderSaved = SaveOrderToDB(order); // Generate order Id. string orderKey = $"Order:{order.OrderID}"; if (isOrderSaved) { // Generate an instance of Key Dependency. CacheDependency dependency = new KeyDependency(customer.CustomerID); // Create CacheItem to with your desired object CacheItem cacheItem = new CacheItem(order); // Add Key Dependency to order cacheItem.Dependency = dependency; // Add order in cache with dependency on the customer added before cache.Add(orderKey, cacheItem); } OrderDetail orderDetail = new OrderDetail { OrderID = order.OrderID, UnitPrice = 200, Discount = 25.5F, Quantity = 10, }; // Save order's detail to database. bool isOrderDetailSaved = SaveOrderDetailToDB(orderDetail); if (isOrderDetailSaved) { // Generate order detail Id. string orderDetailId = $"OrderDetail: {orderDetail.OrderID}"; // Generate an instance of Key Dependency. CacheDependency dependency = new KeyDependency(orderKey); // Create CacheItem to with your desired object CacheItem cacheItem = new CacheItem(orderDetail); // Add Key Dependency to order cacheItem.Dependency = dependency; // Add order in cache with dependency on the customer added before cache.Add(orderDetailId, cacheItem); } |
4. Caching Database Query Results (Transactional Data)
Most of the times, data from the database is returned as query result sets. This strategy suggests that you insert the whole result set into the cache so that successive queries are served from the cache. Since, queries with the same criteria will always refer to the same result set.
The following code shows how query result sets can be cached as collections:
|
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 |
// Items with Groups exist in cache // Custom class is query indexed through the NCache Management Center or config.ncconf // Search for items with Group // Provide Fully Qualified Name (FQN) of your custom class string query = "SELECT ProductID FROM FQN.Customer WHERE $Group$ = ?"; /// Use QueryCommand for query execution var queryCommand = new QueryCommand(query); // Providing parameters for query queryCommand.Parameters.Add("$Group$", "Electronics"); // Executing QueryCommand through ICacheReader ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand); // Check if the result set is not empty if (reader.FieldCount > 0) { while (reader.Read()) { string ProductID = reader.GetValue(1); // Perform operations } } else { // Null query result set retrieved } |
Conclusion
As you can see, caching a database in .NET applications with a cache like NCache is quite easy. Based on usage patterns in the application, you can cache and retrieve your data via queries as if doing it for a database. Furthermore, NCache is a in-memory distributed caching solution instead of a regular cache, so any potential database issues are easily handled. So, what are you waiting for? Download NCache today!
