SQL Search: Behavior and Usage Overview

Note

This feature is only available in NCache Enterprise Edition.

NCache is a key-value cache where every new object is stored against a unique key. Similar to a relational database where the primary key is used to find data, NCache allows you to retrieve data by specifying the corresponding key. However, many practical scenarios require a flexible searching capability to acquire data based on different attributes other than just the primary key. Since most high transactional applications keep data in the cache, the ability to query data is highly desirable.

Importance of Searching in Cache

Assume that there is an e-commerce website where the products are searched based on a range of criteria such as category, price, weight, etc. To improve the application performance, using a distributed caching system seems to be a befitting solution. However, losing the searching capability or rewriting the application is not acceptable.

In such a scenario, NCahce is an ideal distributed cache that can scale the application performance without compromising the search capability. Specifically, NCache enables you to query cached data based on different attributes using SQL-like query syntax. By using NCache in the aforementioned scenario, you can search for all the products by using category, price, or weight as your criteria.

Note

NCache allows to search and delete data in cache using SQL-like query syntax.

NCache provides the flexibility to retrieve projections, the whole object, the cache keys only, or the key-value pair itself based on your requirement. Moreover, NCache also supports the basic query operators, logical operators, and aggregate functions provided by SQL.

How it Works

In NCache, the data is distributed across different cluster nodes due to partitioning. Searching for a customer using its ID might be easy enough when there are a thousand customers. However, finding one in a million would not have the same complexity when the records are distributed across different nodes and the search criteria can be any attribute of the customer. In this context, SQL search in cache requires an efficient data searching mechanism.

In particular, NCache employs indexing and distributed queries in order to support real-time SQL-like searching capability in the cache. Indexing makes an attribute searchable in the cache. Once an item is added against an index, it is returned as a result of a query on the node to which the client is connected. Meanwhile, a query is executed in a distributed fashion when data is distributed in the partitioned topology.

Important

An attribute must be indexed in order to be searchable in the cache.

Indexing

Traditional databases use indexing to boost query performance. Howevere, NCache uses indexing as a madndatory to enable searching through SQL-like queries. In order to query data in the cache, all searchable attributes need to be indexed first. It is important to note that the fields/attributes specified in projections and criteria must be indexed. Once an index is defined for an attribute, any relevant data added to the cache is automatically indexed and becomes searchable. Hence, defining indexes once is sufficient.

Note

Read operations are much faster due to indexing, but there is an overhead for each write operation.

NCache uses indexing since it is a performance oriented approach. Indexing eliminates the need to search every entry in the cache while searching for a query result set. This expedites the searching mechanism and meanwhile saves time and computing power.

Warning

Do not index unnecessary fields since it would result in memory and performance overhead.

Distributed Queries

The query execution in NCache varies based on cache topology and the underlying client connectivity. In case of the mirror or replicated topology, the query is executed only on the node with which the client is connected. However, this is not the case when data is partitioned across the cluster nodes.

The queries over a partitioned cluster are executed in a distributed manner primarily due to data partitioning. Query is executed individually on each node since client is connected to all the cluster nodes. The result set consisting of the required data is returned from each node and merged before returning the final result to the client. Here, the performance also relies on the number of cluster nodes and the size of the result returned by each node.

Note

Distributed query in NCache is highly scalable.

The distributed query yields higher scalability in NCache due to partitioned data and parallel execution. When new nodes are added to the cluster, the partitioned data for each member is reduced. As a consequence, the query execution time spent by each server node reduces. Moreover, the network traffic also reduces due to the concurrent execution on each node.

SQL Search in NCache

NCache allows you to search and delete cache data using SQL-like SELECT and DELETE queries, respectively. INSERT and UPDATE operations are currently not supported. ExecuteReader is used for executing SELECT query in cache and returns the result set to an instance of ICacheReader. ExecuteNonQuery is used for deleting items from the cache. You can query all primitive data types and nullable types provided that query indexes have been defined.

Important

Make sure to close the instance of ICacheReader after execution, as it is necessary to clean the resources.

The querying language lets you specify non-primary key criteria depending on which the data is required. You can execute queries to search data in the cache using different projections and criteria. However, the attributes specified in projections and criteria must be indexed. Moreover, based on your requirement, you have the flexibility to retrieve the cache keys only or whole items fulfilling the given query criteria.

Indexing Types

If you want to search an attribute in the cache, it should first be indexed. NCache provides several flexible ways for indexing. Keeping in view the need for predefined (static) indexes, NCache allows you to manually define indexes for searchable attributes in advance. One way to define static indexes is through configuration. Alternatively, you can also define indexes programmatically.

On the other hand, dynamic indexes such as Groups, Tags, and Named Tags are automatically created at runtime for an attribute. Run time indexes allows you to query data in the cache based on logical groups, tags, and named tags.

Search Using SQL Projections

NCache allows you to retrieve all the indexed attributes of a class or specific projections from the cache store based on your query. NCache supports different projections to facilitate efficient searching. Projections can play an important role in the performance enhancement of your application when it comes to queries. For instance, you want to retrieve only the available units column for products belonging to a specific category. In that case, you can use the following SELECT query.

SELECT UnitsInStock FROM FQN.Product WHERE Category = "Sample_Category"

Note

For performance enhancement, it is recommended to query desired columns only since returning the complete object has an additional cost.

It is worth noticing that, by retrieving only the desired columns the additional overhead of retrieving all the indexed attributes of the Product class is avoided. In this way, NCache facilitates the users to achieve enhanced performance through improved query syntax.

Search for Keys and Objects

Based on your requirement, you can either retrieve the cache keys only or the associated data as a result of your query. In case you need to enumerate the keys only, NCache allows you to retrieve keys only. It is an efficient approach since the size of data to be fetched from cache significantly reduces this way. Hence, you can use it for enhanced performance.

ExecuteReader has an option getData which needs to be set as false for the retrieval of keys only. By default, its value is true. In case you need data for further processing, the defaut value can be used to search for objects.

Supported Query Operators

You can use a rich set of operators provided by NCache according to your required query criteria to fetch desired result sets from your cache.

The following operator categories are supported in NCache queries:

Category	Operator
Basic Query Operators	= , == , != , <> , < , > , <=, >=, IN, LIKE , NOT LIKE
Logical Operators	AND, OR , NOT
Aggregate Functions	SUM, COUNT, AVG, MIN, MAX
Miscellaneous	GROUP BY, ORDER BY, DateTime.Now, DateTime ("any date time compatible string")

For more detail, please refer to the Query Operators section.

Client Cache

NCache assumes that the client cache (L1 cache) might have partial data. Keeping in view that complete data is not guaranteed in the L1 cache, the queries are always executed on L2 cache in NCache. If L1 cache is enabled on the client-end executing the query, the query execution behavior will not be effected. The query will be directly executed on the server cache (L2) as usual. However, when data is changed in clustered cache (L2) due to DELETE query, L1 synchronizes itself with L2 cache.

Although client cache has no influence on query execution in NCache, it can certainly play an important role in performance enhancement. For instance, you want to perform further operations on the query result set, but fetching the data from the cache might hurt your application performance. In that case, if client cache is configured with L2 cache, you can simply retrieve the desired data with a single GetBulk call. This saves time and avoids performance degradation since operations performed on the client cache are much faster as compared to L2 cache.

In This Section

Define Indexes Programattically
Explains how to define indexes programmatically.

SQL Search for Keys Syntax and Usage
Discusses how to search the cache for keys according to the query criteria.

SQL Search for Objects Syntax and Usage Syntax and Usage
Discusses how to search the cache for objects according to the query criteria.

SQL IN Operator Syntax and Usage
Discusses how to use the IN operator in SQL like format.

SQL Like Operator Syntax and Usage
Discusses how to use the LIKE operator in SQL like format.

SQL GROUP BY Syntax and Usage
Discusses how to use the GROUP BY clause in SQL like format.

SQL ORDER BY Syntax and Usage
Discusses how to use the ORDER BY clause in SQL like format.

SQL Delete Statement Syntax and Usage
Discusses how to delete the items from the cache using SQL like format.

SQL Datetime Function Syntax and Usage
Discusses how to use the Date functions for querying the data.

SQL Logical Operators Usage
Discusses how to use the logical operators for querying the data.

SQL Reference for NCache
Explains the SQL syntax and all the query formats supported by NCache.