SQL Search in NCache is an in-memory querying mechanism that allows developers to search distributed cache data using SQL-92 compliant syntax. By utilizing the SearchService API, applications can perform complex criteria-based searches directly on cached objects, significantly reducing database overhead and latency.
NCache’s SQL-like queries bring the familiar power of SQL search to in-memory caching, enabling efficient, criteria-based data retrieval directly from the cache. Essentially, this feature allows developers to perform complex searches and apply filters directly on cached data. Thus, eliminating the need for repeated database access. This results in faster data retrieval and better application performance. These queries are ideal for application scenarios with reference data that needs quick access through various search criteria, like dashboards or reports.
By caching complete datasets, NCache delivers precise and consistent results, making it an ideal choice for applications needing fast, scalable, and accurate data retrieval. This blog will discuss how SQL-like queries work in NCache and walk through examples to demonstrate their use in applications, ensuring improved data handling.
Key Takeaways
SQL-92 Compliance: Leverages familiar SQL-92 syntax to perform complex criteria-based searches on in-memory data, reducing the learning curve for developers.
Reduced Database Latency: Eliminates the need for frequent database trips by filtering and retrieving data directly from the cache, significantly boosting application response times.
Flexible API Support: Offers specialized execution methods (ExecuteReader, ExecuteScalar, and ExecuteNonQuery) to handle everything from large result sets to single-value aggregations and bulk deletions.
Scalable Performance: Queries are executed in parallel across the distributed cluster, ensuring search operations remain fast and efficient as your data volume grows.
Why Use SQL-like Queries in a Distributed Cache?
NCache’s SQL-like search capability offers a powerful way for users to access and manage cache data, enabling applications to get faster results based on a specific-criteria. Some of the key benefits that NCache SQL queries offer are listed below.
- Improved Performance: Querying data directly from the cache significantly reduces expensive network trips, minimizing latency, and improving overall application performance. This is essential in scenarios with numerous read requests, where speed is critical.
- High Scalability: NCache’s distributed architecture allows applications to scale horizontally by adding multiple cache servers. This scalability supports parallel query execution, enabling the system to handle increased loads and maintain performance under high demand.
- Indexed Search: NCache supports indexing of cached data, facilitating efficient and rapid search operations. By querying indexed data, applications can achieve swift data access, contributing to overall system responsiveness.
Quick Setup Guide
Ensure that you have installed NCache on the servers and caches are already configured. After that you need to setup the environment accordingly, to configure and implement this powerful feature. This section guides you through setting up your application, configuring query indexes, and implementing SQL searches.
Install the NuGet Package
To get started, you need to install the NCache SDK NuGet package by executing the following command in the Package Manager Console.
|
1 |
Install-Package Alachisoft.NCache.SDK –Version x.x.x |
Include NCache Namespaces
Import the necessary NCache namespaces Alachisoft.NCache.Client and Alachisoft.NCache.Runtime.Caching, to enable cache operations in your application.
Configure Query Indexes
Next, you need to configure the query indexes. You can do so by running the following command:
|
1 |
Add-QueryIndex -CacheName demoCache -AssemblyPath C:\Data.jar -Type Data.Customer -Attributes "ProductName'$' UnitPrice" |
This command adds a query index for the attributes ProductName and UnitPrice in cache. You can also define indexes programmatically or using NCache Management Center.
Implement SQL Query Commands
Next, you need to implement SQL Query commands within the application. First, initialize the cache by establishing a connection and retrieve the cache name from the app.config file. The application calls the GetCache API to obtain the cache handle, then proceeds to add sample data into the cache. This setup enables efficient SQL-like queries directly on cached data, optimizing search and retrieval performance.
How to Execute SQL Queries in NCache (APIs)?
Following are the key commands offered by NCache for efficient data retrieval. Each method is designed for a specific interaction pattern with your cached data:
| Method | Return Type | Primary Use Case |
|---|---|---|
| ExecuteReader | ICacheReader | Retrieving multiple rows/objects or large datasets. |
| ExecuteScalar | Object (Single Value) | Aggregate functions like COUNT, AVG, or SUM. |
| ExecuteNonQuery | Integer | Data manipulation, specifically DELETE operations. |
Execute Reader
NCache enables SQL-like criteria-based searches through the ExecuteReader API, allowing direct, in-memory data retrieval. Define a SQL-like query string, convert it into a query command via NCache’s query API, and execute it using SearchService.ExecuteReader. This approach brings powerful search capabilities to your application with efficient, familiar syntax.
The following code demonstrates how to use NCache’s ExecuteReader API for querying data directly from the cache based on specific criteria.
|
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 |
string query = "SELECT ProductName, UnitPrice From FQN.Product WHERE ProductName LIKE ?"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); queryCommand.Parameters.Add("ProductName", "choco*"); // Executing QueryCommand through ICacheReader ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand); // Read results if result set is not empty if (reader.FieldCount; 0) { while (reader.Read()) { string productName = reader.GetValue<string>("ProductName"); double unitPrice = reader.GetValue<double>("UnitPrice"); Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}"); } } else { Console.WriteLine($"No product found having a name that starts with 'choco'."); } |
getData: false to retrieve only the cache keys.The code uses ExecuteReader in NCache to run a SQL-like query with parameters, filtering products by names starting with “choco” using the LIKE operator. The queryCommand holds the query and parameters, allowing efficient cache searches. By default, getData is true, retrieving both keys and full object data. Setting getData to false can return only keys, optimizing performance. Additionally, chunkSize enables data retrieval in batches, aiding memory management for large result sets. NCache also supports the use of IN, GROUP BY, ORDER BY, DateTime, and Logical Operators with its ExecuteReader API.
To know more about the NCache supported query operators please check our SQL Reference guide.
Execute Scalar
Along with ExecuteReader, NCache provides another powerful API to search cached data called ExecuteScalar. This API helps you retrieve a single value against the specified criteria. It executes the user queries based on the defined parameters and returns the first column of the first row, ignoring any additional columns or rows.
The following code demonstrates how NCache’s ExecuteScalar API works.
|
1 2 3 4 5 6 7 8 9 10 11 |
string query = "SELECT AVG(UnitPrice) FROM FQN.Product WHERE Category = ? AND UnitsInStock > ?"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); queryCommand.Parameters.Add("Category", "Beverages"); queryCommand.Parameters.Add("UnitsInStock", 10); // Example condition for units in stock // Executing QueryCommand through ICacheReader decimal average = (decimal)cache.SearchService.ExecuteScalar(queryCommand); Console.WriteLine($"Average price of Beverages with more than 10 units in stock is {average}."); |
The search process is almost similar to what we have been following in ExecuteReader. Starting with specifying the criteria for search in the form of query string. In this case, we are selecting the average UnitPrice based on specific criteria. We provide this query string to a QueryCommand instance, which is then passed to the ExecuteScalar method for execution.
Execute NonQuery
NCache also enables its users to delete cached data based on the given criteria. For this purpose, the ExecuteNonQuery API is used, which runs the DELETE statement and returns the number of rows affected by the query, as demonstrated below.
|
1 2 3 4 5 6 7 8 9 10 11 |
// Precondition: Cache is already connected // Items are already present in the cache // Provide Fully Qualified Name (FQN) of your custom class string query = "DELETE FROM FQN.Product WHERE UnitsInStock = ?"; // Use QueryCommand for query execution var queryCommand = new QueryCommand(query); queryCommand.Parameters.Add("UnitsInStock", 0); // Execute query _cache.SearchService.ExecuteNonQuery(queryCommand) |
The given code deletes items from the NCache data store where UnitsInStock is 0 using a DELETE query executed with _cache.SearchService.ExecuteNonQuery().
Conclusion
NCache’s SQL-like query capabilities bring a robust, database-style experience directly to in-memory caching, supporting seamless data retrieval and management. By leveraging APIs such as ExecuteReader, ExecuteScalar, and ExecuteNonQuery, developers can execute complex searches, single-value retrievals, and data deletion operations efficiently.
Moreover, NCache offers users the ability to group data based on attributes that are not present in the cache, such as tags or other custom attributes, while using it’s SQL Search feature to search through them, making data management a breeze. So, what are you waiting for? Download NCache today!
Frequently Asked Questions (FAQ)
Q: Can I run SQL queries on NCache without defining indexes?
A: No. Unlike traditional relational databases, NCache requires predefined query indexes for any attribute you wish to search. This ensures that queries are executed at in-memory speeds without performing costly full-cache scans.
Q: Does NCache SQL Search support standard JOIN operations?
A: NCache SQL syntax focuses on querying attributes within a single object type. For relational data, it is a best practice to use NCache’s Cache Dependency features or to denormalize your domain objects to maintain relationships within the cache.
Q: What is the performance impact of using ExecuteReader vs. ExecuteScalar?
A: ExecuteScalar is highly optimized for aggregations (like COUNT or SUM) because the calculation is performed on the server nodes, returning only a single value to the client. ExecuteReader is designed for retrieving multiple records and is best used with chunkSize for large result sets to prevent memory spikes.
Q: Is NCache SQL Search case-sensitive?
A: While the SQL keywords (like SELECT or WHERE) are case-insensitive, the attribute names and string values used in your query parameters must match the case defined in your indexed classes to return accurate results.
