Databases have been an integral part of .NET applications as you can fetch and manipulate large sets of data efficiently. However, when dealing with high transactional data, databases bring about performance degradation in your .NET applications. This is because databases cannot be scaled out to multiple machines – you can have only one server machine dedicated to the database tier and cannot add more. The database thus becomes a performance bottleneck. To solve this problem, we use a distributed caching solution, such as NCache to mitigate unnecessary and expensive trips to the database.
NCache Details Download NCache Edition Comparison
How to Cache Database?
Caching a database is much simpler than it sounds. You need to incorporate a caching tier with a cache like NCache between your application and database tier so that database data is served from the cache. For ease, the four most common patterns of cache use have been summed up. They are briefed below:
1. Object Caching
This strategy suggests that if your data does not exist in the cache, you fetch it from the database and then insert it into the cache, such as NCache. Successive calls are dealt with from the cache afterward. For issues regarding stale data, you add expiration to the cache item so that it can be refreshed from the database.
The following piece of code fetches an instance of Customer from the cache if it exists. Otherwise, it fetches it from the database and adds it to cache for a cache hit in successive calls.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
var key = "Customer:1001"; // Get Customer from cache if it exists var customer = cache.Get<Customer>(key); // If customer does not exist in cache if (customer == null) { // Fetch from database customer = GetCustomerFromDB(1001); // Create sliding expiration of 15 seconds. Cache removes item after this time var expiration = new Expiration (ExpirationType.Sliding, TimeSpan.FromSeconds(15.0)); var cacheItem = new CacheItem(customer) { Expiration = expiration }; // Insert the item with expiration into cache cache.Insert(cacheKey, cacheItem); } |
NCache Details Data Caching Docs NCache Programmer’s Guide
2. Cache Reference Data and Use SQL Queries to Search it
Data that is only read and not changed frequently is known as reference data. It is quite common for a .NET application to repeatedly read data and infrequently write data to the database. The cache (like NCache) is more powerful when it is used for reference data as it works the fastest with such data.
It is highly advised to cache the entire reference data and then use SQL queries to search the cache for it instead of going to the database. Please note that if you don’t cache the entire reference data-set (e.g. all the customers) then your SQL queries against the cache will return invalid results because some of the data is only in the database.
If you’ve already preloaded the cache with all your reference data then the following example explains how you can search for it:
|
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 |
// Pre-Requisite: All customer instances have already been added into cache var customerName = "John Smith"; // Write SQL query for cache var query = "SELECT $Value$ FROM Northwind.Model.Customer WHERE Name = ?"; // Create the query command that cache understands from the query var queryCommand = new QueryCommand(query); // Provide the parameters for the query queryCommand.Parameters.Add("Name", customerName); // Execute the query on cache var reader = cache.SearchService.ExecuteReader(queryCommand, true); var customer; // If a valid projection was made in the query if (reader.FieldCount > 0) { while (reader.Read()) { customer = reader.GetValue<Customer>(1); // Perform operation according to business logic } } |
NCache Details SQL Caching Docs Data Caching Docs
3. Handling One-to-Many Relations in the Cache
Mostly, our data contains relations among it. For example, in the context of 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 .NET application. Using a cache such as NCache, you can relate these entities within the cache too.
This is done by associating all related entities with a tag identifier so that instead of fetching each order of customers individually, you can fetch all orders of the customer in a single call by searching the cache via the tag identifier. The given example code illustrates how we can bring about this behavior:
|
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 |
var cacheKey = "Customer:1001"; var tagName = "Customer:1001_Orders"; // Check if customer exists in cache var cachedCustomer = cache.Get<Customer>(cacheKey); // If customer does not exist in cache if (cachedCustomer == null) { // Fetch from database var customer = GetCustomerFromDb(1001); // Insert customer into cache cache.Insert(cacheKey, customer); // Fetch orders of the customer from database var relatedOrders = GetRelatedOrdersFromDb(customer); // Create a tag for all orders of the customer var tag = new Tag(tagName); foreach (Order order in relatedOrders) { var orderCacheKey = $"Customer:1001_Order:{order.OrderID}"; // Create a cache item and set the tag identifier var orderCacheItem = new CacheItem(order) { Tags = new[] { tag } }; // Insert the tagged cache item into cache cache.Insert(orderCacheKey, orderCacheItem); } } else { // Item exists in the cache // Create a tag to fetch the related orders var tagIdentifier = new Tag(relatedEntityIdentifier); // Fetch the related orders based on the tag identifier var cachedOrders = cache.SearchService.GetByTag<Order>(tagIdentifier); } |
NCache Details Cache Tagged Data Docs Data Caching Docs
4. Caching Database Query Results (Transactional Data)
Most of the time, data from the database is returned in the form of 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. Queries with the same criteria will always refer to the same result set. Therefore, we can consider query commands themselves as the unique identifier for the result set and use it as a key in the cache.
The following piece of 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 |
// Query to select East Coast Customers var query = "SELECT * FROM Northwind.Model.Customer WHERE Region = ‘EastCoast’"; // Create cache key based on the query var key = query; // Fetch the entities based on query var resultSet = cache.Get<IList<Customer>>(key); // If data against query not found in cache if (resultSet == null) { // Fetch the result set from database var customers = GetCustomersFromDb(query); // Convert the result set into a collection var cacheReadyCustomers = customers.ToList(); // Add result set to cache against query cache.Insert(cacheKey, cacheReadyCustomers); } |
NCache Details Data Caching Docs SQL Caching Docs
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 your data and fetch it via queries as if doing it for a database.
Furthermore, NCache is an in-memory distributed caching solution. All the issues encountered when using a database are handled in it and it has all implementations for a cache with standard requirements and much more.
