SQL Search Behavior and Usage Overview

Note

This feature is only available in NCache Enterprise Edition.

Your application typically uses cache as a key value store where everything is stored based on a key and you must have this key to fetch an item. This is like having a relational database where you can use only the primary key to find data. This works fine in many situations but a real life complex application often needs to find data based on attributes other than just the primary key. And since you are keeping a lot of your data in the cache, it would be very useful if you could search the cache in this manner as well.

A basic example of this would be an e-commerce website that needs to fetch all products based on criteria such as category, price, and weight etc. You cannot specify category based keys for all products and hence cannot store the list of those keys for retrieval.

For the scenario highlighted above, you just have to select all the products related to the required category and it will return all related keys or items.

Sample query: "SELECT * FROM Sample.Data.Product WHERE Category = "Sample_Category"

Note

NCache also supports backward compatibility for the older query syntax.

Older Format: "SELECT FQN.Product WHERE this.Category = ?"

For searching and deleting data, NCache provides you with its own powerful cache searching mechanism where you can fetch the cache keys/data using SQL like query syntax. Insert and Update operations are currently not supported.

The querying language lets you specify non-primary-key criteria depending on which the data is required.

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.

Note

NCache's SQL like query Language is not case sensitive.

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 DateTime.Now , DateTime ("any date time compatible string")

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

Topology Wise Behavior

Mirrored and Replicated Topology

In the Mirror and Replicated topology of NCache, a query is executed on the Active node and server node where client is connected respectively. The execution operation for DELETE query is replicated to the replica node(s) for data consistency.

Partition and Partition of Replica

In Partitioned and Partition-of-Replica topology of NCache, a query is executed on all of the partitions and active-partitions respectively. In the case of POR if there is a replica of the partition, the active-partition replicates the operation performed by the query to the replica in case of DELETE query.

Client Cache

Any search or delete operation by the means Querying in NCache is executed on the server-side. It means that if a client cache (L1) is enabled on the client-end which is executing the query will not affect the behavior of the query execution, it will be directly executed on the server cache (L2). Whereas in case of DELETE query, when data is changed in cluster cache (L2), L1 synchronize itself with (L2).

See Also

Query Operators
Delete SQL Statements
Search Cache with LINQ
SQL Search for Keys