Using SQL Queries with Distributed Cache

As businesses scale, they increasingly rely on distributed caching solutions to manage high traffic and ensure fast data retrieval. However, one of the key challenges in implementing a distributed cache is efficiently handling complex SQL queries. Traditionally, e-commerce platforms and enterprise applications use SQL queries for product searches, transaction processing, and inventory management. But, if these queries bypass the cache and access the database directly, the benefits of caching are significantly reduced.

To address this challenge, NCache provides robust SQL-like query support directly within the cache, allowing you to optimize performance without altering your existing query structure. With its latest enhancements, NCache now offers improved LINQ integration, support for .NET 8, and enhanced indexing mechanisms for even faster query execution.

Cache as a Datastore

Storing data in a distributed cache significantly improves application responsiveness. However, efficiently querying this stored data is crucial, and searching the specified product, as discussed above, requires executing SQL queries. With NCache’s improved query engine, you can perform SQL-like searches directly in the cache without relying on the database, minimizing database trips and handling millions of concurrent requests seamlessly.

Executing SQL Queries in NCache – A Quick Example

Let’s consider a scenario where a customer frequently purchases a specific product. The query below is executed directly in the cache using the latest Query Service in NCache, which now supports improved indexing and enhanced data structures for rapid query execution:

// Ensure items are already stored in the cache

string query = "SELECT * FROM FQN.Product WHERE ProductID > ?";

// Create QueryCommand object

var queryCommand = new QueryCommand(query);

// Add query parameters

queryCommand.Parameters.Add("ProductID", 50000);

// Execute query using the enhanced ICacheReader

ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

if (reader.FieldCount > 0)

{

while (reader.Read())

{

string result = reader.GetValue<string>("ProductID");

}

What Type of Data is Searched in Cache?

NCache classifies cached data into two primary categories:

Transactional Data

Transactional data is frequently updated and is used for quick processing. A query example fetching products priced below $5 is shown below:

1	SELECT * FROM FQN.Products WHERE UnitPrice <= 5.00

With NCache’s optimized indexing, this data is processed rapidly, reducing database dependencies.

Reference Data

Reference data is structured and stored in the cache for extended periods. It remains synchronized with the database and supports SQL queries for data retrieval. With NCache’s new auto-synchronization feature, reference data remains up to date without manual refreshes.

To ensure SQL queries on cached data are valid, it is essential to cache entire datasets (e.g., all products or all customers). SQL queries can only return accurate results if all the relevant data is present in the cache. If only a portion of the dataset is cached, executing SQL queries may yield incomplete or incorrect results. By caching full datasets, applications can fully leverage NCache’s in-memory querying capabilities, ensuring consistency and accuracy of the retrieved data.

Latest SQL Features in NCache

NCache has introduced several new SQL-like enhancements in its recent releases to improve efficiency.

Using SQL Wildcards in NCache

SQL wildcards in NCache allow more flexible searches using the LIKE operator.

string query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ?";

var queryCommand = new QueryCommand(query);

queryCommand.Parameters.Add("ProductName", "Cha*" );

ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

The * wildcard represents zero or more characters, enabling searches like “Cha*”, which matches “Chair”, “Charger”, etc.

Using SQL Aggregate Functions in NCache

With support for SUM, COUNT, AVG, MIN, and MAX, aggregate functions now run even faster with the improved indexing system.

string query = "SELECT COUNT(*) FROM FQN.Products WHERE UnitPrice > 5";

var queryCommand = new QueryCommand(query);

ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

Using SQL GROUP BY in NCache

The GROUP BY clause enables better organization of query results:

string query = "SELECT Category, COUNT(*) FROM FQN.Products WHERE UnitPrice > 5 GROUP BY Category";

var queryCommand = new QueryCommand(query);

ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

Why NCache is Ideal for SQL Queries in .NET 8 Applications

By leveraging the latest features of NCache, .NET applications can efficiently handle SQL queries without sacrificing performance. Here’s why:

Seamless Integration with .NET 8: With support for .NET 8 and Entity Framework Core caching, developers can now integrate distributed caching with modern applications effortlessly.
No Additional Code Required: The new LINQ-based query support allows developers to execute cache queries using familiar LINQ syntax.
Optimized Query Performance: The updated indexing engine reduces query execution time, making search operations almost instantaneous.
Lower Bandwidth Costs: With fewer database queries, applications consume less network bandwidth, improving overall efficiency.
Auto-Sync for Reference Data: Reference data stored in the cache remains synchronized with minimal maintenance effort.

Predefined Indexes: A Must for NCache Queries

Unlike traditional databases where queries can run without predefined indexes, NCache requires defining indexes beforehand to enable high-speed queries. This ensures that only the relevant dataset is searched, enhancing query execution times.

Conclusion

By integrating SQL query capabilities directly into the cache, NCache revolutionizes data handling in .NET applications. With enhanced query execution, LINQ support, auto-sync capabilities, and optimized indexing, developers can now achieve maximum performance without relying heavily on database calls. If you’re looking to enhance your .NET application’s performance while reducing infrastructure costs, NCache provides the ideal distributed caching solution.

Explore NCache today and experience the power of high-performance SQL queries in distributed caching!