When scaling an e-commerce application, improving performance and efficiency often leads businesses to consider adopting a distributed cache system. While this can enhance speed and handle increased traffic, it also introduces new challenges, especially concerning data retrieval and query processing. For instance, a common issue arises around how a distributed cache handles complex data queries, particularly SQL-like searches. Since e-commerce platforms rely on intricate SQL queries for product searches, user transactions, and inventory management. Rewriting these queries or letting them bypass the cache and hit the database directly can be daunting. If SQL queries still go to the database, it could undermine the cache’s effectiveness.
Luckily, NCache provides a quick and reliable fix to all such performance issues. It supports SQL-like data queries directly within the cache, enabling you to enhance performance without altering your existing query structure.
Cache as a Datastore
Storing data can significantly improve performance, but it introduces the challenge of querying this data efficiently. For instance, searching the cache for specific products requires querying the entire inventory. Fortunately, NCache supports SQL queries, allowing you to perform data queries in your cache just like you would with a traditional database.
Figure 1: NCache handling SQL query load
As shown in Figure 1, NCache efficiently handles SQL query loads by storing the entire product catalog in the cache. This means that SQL queries can be executed directly on the cached data, significantly reducing the need to access the database and handling hundreds of thousands of concurrent customers seamlessly.
SQL Queries in NCache – Quick Example
Suppose a customer has the product ID of a product of which he is a frequent consumer. The query shown below will execute directly in the cache (not in the database) and will 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 |
// 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 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<string>(0); // Perform operations using the retrieved keys } } else { // Null query result set retrieved } |
What Type of Data is Searched in Cache?
NCache categorizes data into two main types, i.e., transactional data and reference data, as discussed below.
Transactional Data
Transactional data is treated as a singular unit to fulfill a request. This type of data, retrieved from the database, is stored in the cache for quick access. For instance, given the table from the database, the customer asks for all the products cheaper than $5, and the resulting output stored in the cache is transactional data.
Figure 2: Products table in the database
|
1 |
SELECT * FROM FQN.Products WHERE UnitPrice <= 5.00 |
Figure 3: Query result stored in the cache
Transactional data is stored temporarily in the cache for processing. It is frequently updated, with old or unnecessary data being discarded. Since transactional data represents the results of queries, it is accessed via key searches rather than SQL queries, as SQL is not needed for individual objects.
Reference Data
NCache supports storing large amounts of data, known as reference data, in the cache for longer periods. This data is structured, relational, and regularly synchronized with the database. Due to these reasons, it is possible to implement SQL queries on this data in the distributed cache.
By implementing NCache, most customer queries are processed directly within the cache, providing immediate results. This reduces the number of trips to the database, effectively eliminating performance bottlenecks and optimizing overall efficiency.
SQL Features in NCache
NCache provides a wide range of SQL functions and operators for searching and deleting data within the cache. However, it’s important to note that the SQL extensions provided by NCache do 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 enhances search flexibility with two wildcards in the SQL LIKE function:
- ?: Represents a single character. For example, “shar?” will match results like share, sharp, or any other variation with a single character following “shar”.
- *: Represents zero or more characters. For example, “cha*” will match results like char.
|
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 |
string query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); // Adding Parameters ArrayList categoryList = new ArrayList(); categoryList.Add("Beverages"); categoryList.Add("Produce"); queryCommand.Parameters.Add("Category", categoryList); queryCommand.Parameters.Add("UnitsInStock", 100); queryCommand.Parameters.Add("UnitPrice", 100); // 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()) { // Get the value of the result set string result = reader.GetValue<string>("ProductID"); } } else { // Null query result set retrieved } |
Using SQL Aggregate Functions in NCache
Aggregate functions in SQL 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 include SUM, COUNT, AVG, MIN, and MAX.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// 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. However, you can only use it in queries that include aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
// 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 Ideal for SQL Queries in .NET Apps
Returning to our superstore scenario, by implementing NCache to store the product catalog and executing SQL queries directly in the cache, the application significantly reduces the need for costly trips to the database. Thus, this not only enhances performance but also offers several other benefits:
- No Additional Code: With most queries handled within the cache, there’s no need to write additional code to access the database repeatedly.
- 100% Native .NET: NCache is a fully native .NET distributed cache, enabling you to store, manage, and query your data reliably—unlike any other distributed cache and datastore.
- Lower Bandwidth Costs: By performing queries on cached data and returning results directly to the application, NCache saves significant time and reduces bandwidth costs.
- Extremely Fast and Reliable: NCache is designed to be an incredibly fast and scalable distributed cache, specifically optimized for .NET applications. It can handle up to two million transactions per second, as shown in the NCache benchmark.
As you can see, NCache offers a total win-win solution for your .NET applications. For more details on using SQL in your distributed cache, head over to the NCache documentation.
Predefined Indexes: A Must for NCache Queries
Unlike databases where queries can run without predefined indexes, NCache requires defining indexes beforehand to enable query execution. This requirement ensures high-speed and efficient data retrieval tailored to your query patterns. As all searchable attributes are indexed, you will not need to scan the entire dataset. You can read more about it in this blog.
Conclusion
By integrating SQL query capabilities directly into the cache, NCache transforms how .NET applications handle data, offering a seamless way to boost performance without the overhead of constant database access. This approach not only simplifies your architecture but also delivers speed, reliability, and efficiency. If you’re looking to enhance your application’s performance while reducing costs and complexity, NCache provides the perfect solution. Explore NCache today and experience the difference it can make in your .NET environment.
