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.
Benefits of SQL Search in NCache
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.
Executing SQL Queries
Following are the key commands offered by NCache for efficient data retrieval.
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'."); } |
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!
