A friend of mine said that his superstore management is considering going for a distributed cache system to improve the performance of their E-Commerce application. But he wasn’t sure if this distributed cache would provide them the ability to search data with SQL. The reason is that most of their online store functionality depends on searching data and they cannot afford to rewrite their application. And, if all their SQL queries end up still going to the database, then it defeats the purpose of using a distributed cache.
If you are struggling with a similar situation and need a quick and reliable fix to all your performance problems then NCache is exactly what you need. Read on to find out more.
NCache Details Download NCache Edition Comparison
Cache as a Datastore
Now if we are storing data in a cache to make it available for faster usage, the problem my friend explained still remains; the customer wants to query the inventory to search for the desired products. Well, I have good news for you (and him); NCache is completely equipped with SQL which allows you to query data in your cache the same way as you would in your database.
Figure 1: NCache handling SQL query load
Wait… hold on! Doesn’t this suddenly solve all of our problems? As shown in Figure 1 The entire product catalog is stored in the cache, we can implement SQL queries on it and return the result directly to the application – this reduces the total trips to the datable significantly and since NCache is scalable and reliable, it can handle hundreds of thousands of customers at a single time!
SQL Query in NCache – Quick Example
Suppose a customer has the product ID of a product he is a frequent consumer of, notice the query below, this will execute in the cache (not in the database) and send the result to the application.
|
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 |
// Pre-condition: Cache is already connected // Items are already present in the cache. // Provide Fully Qualified Name (FQN) of your custom class string query = "SELECT ProductName 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); // Check if the result set is not empty if (reader.FieldCount > 0) { while (reader.Read()) { string result = reader.GetValue("ProductName"); // Perform operations } } |
The thing is, these SQL queries in the cache are very helpful however they are working inside a cache after all; so…
What Type of Data is Searched in Cache?
NCache has two vast categories in which data is stored. These are transactional data and reference data.
Transactional Data
Transactional data is data that is treated as a singular unit for the purpose of satisfying a request. These are collected from the database as query results and stored in a cache. For instance, given the table from the database, the customer asks for all the products cheaper than $5, the resulting output stored in cache is transactional data.
Figure 2: Products table in the database
|
1 |
SELECT * FROM Products WHERE UnitPrice <= 5.00 |
Figure 3: Query result stored in the cache
Transactional data is generally stored temporarily for processing. It is frequently updated and old and unnecessary data is discarded. SQL is not needed for a single object and Key searches are used for object data instead. Transactional data is already query result data; therefore, SQL queries cannot be performed on it.
Reference Data
NCache allows you to store large amounts of data in the cache for longer periods of time. This kind of data is known as reference data. Reference data is stored in a structured and relational form, it is regularly kept updated and synced with the database. Due to these reasons, it is possible to implement SQL queries on this data in the distributed cache.
With the implementation of NCache, most of the queries that the customer makes are executed in the cache and results are returned immediately. This reduces the number of trips made to the cache tremendously thereby eliminating excessive trips to the database which removes all performance bottlenecks.
NCache Details SQL Syntax in NCache NCache SQL Docs
SQL Features in NCache
NCache provides a wide range of SQL functions and operators that allow you to search and delete data in the cache. Please note that the SQL extension provided by NCache does not support Insert and Update commands. These commands are directly implemented in the database.
Let’s have a look at some of the unique in-memory SQL features provided by NCache.
Using SQL Wildcards in NCache
NCache provides flexible searching with two distinct wildcards in the SQL function LIKE. These are ‘?’ and ‘*’
“?” is used where there is one character ahead that needs to be searched. For example, “shar?”. This will search the cache and could give share, sharp, or any other similar result.
“*” is used where there could be zero to any number of characters ahead that are searched. For example, “cha*” could give char, character, chair, or any other similar result.
|
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 |
// Pre-condition: Cache is already connected // Items are already present in the cache // Use the Fully Qualified Name (FQN) of your own custom class string query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ? AND Category LIKE ?"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); // Providing parameters for query queryCommand.Parameters.Add("ProductName", "Cha?"); queryCommand.Parameters.Add("Category", "Edib*"); // Executing QueryCommand through ICacheReader ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand); // Check if result set is not empty if (reader.FieldCount > 0) { while (reader.Read()) { string result = reader.GetValue("ProductID"); // Perform operations } } |
Using SQL Aggregate Functions in NCache
Aggregate functions are used in SQL to perform arithmetic operations on multiple values and return a singular value. NCache uses these aggregate functions to perform operations on cached data items.
Some of the aggregate functions supported by NCache are:
SUM, COUNT, AVG, MIN, MAX
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
// Pre-condition: Cache is already connected // Items are already present in the cache // Use the Fully Qualified Name (FQN) of your own custom class string query = "SELECT COUNT(*) FROM FQN.Products WHERE UnitPrice > 5"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand); // Check if result set is not empty if (reader.FieldCount > 0) { while (reader.Read()) { // Get the value of the result set int result = reader.GetValue(1); } } |
Using SQL GROUP BY in NCache
NCache enables you to group data according to your provided criteria using the GROUP BY function. But there is a catch, you can only use group-by in queries that contain any of the above aggregate functions. For example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
// Pre-condition: Cache is already connected // Items are already present in the cache // Use the Fully Qualified Name (FQN) of your own custom class string query = "SELECT Category, COUNT(*) FROM FQN.Products WHERE UnitPrice > 5 Group By Category"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand); // Check if result set is not empty if (reader.FieldCount > 0) { while (reader.Read()) { // Get the value of the result set int result = reader.GetValue(1); string category = reader.GetValue(0); } } |
Reasons NCache is a Good Choice for .NET Apps
Let’s get back to our superstore problem. With the implementation of NCache to store the product catalog and implementing SQL queries on it, now there is a significant reduction in the number of expensive trips the application has to make to the database to make queries and retrieve data.
Here are some of the other benefits of NCache that come along with this:
- No additional code: Since most of the queries are made in cache no additional code has to be written to access the database each time.
- 100% native .NET and .NET core: NCache is a 100% native .NET and .NET core distributed cache that allows you to store manage and query your data in a reliable form, unlike any other distributed cache and datastore.
- Lower bandwidth costs: Queries are performed on the cached data and returned to the application saving a lot of time and bandwidth costs.
- Extremely fast and reliable: NCache is an extremely fast and scalable distributed cache specially designed for your .NET and .NET core applications. Check out the NCache benchmark of two million transactions per second.
So, as you can see, It’s a total win-win situation!
Head over to NCache Docs to know more about how you can use SQL in your distributed cache data store.
