How can I delete items from multiple groups in a single SQL query?

You can use standard SQL OR logic within your WHERE clause, for example: 'DELETE FROM FQN.Customer WHERE $Group$ = 'GroupA' OR $Group$ = 'GroupB'. Alternatively, for better code maintainability, you can execute individual queries sequentially.

Is indexing required for the $Group$ keyword to work in SQL?

Yes. The class being queried must be indexed for SQL queries to work. While groups themselves are not indexed attributes, NCache requires query indexing on the target class to efficiently execute delete queries that use the $Group$ keyword.

Can I combine the group criteria with other standard SQL filters?

Yes, NCache allows combining the $Group$ keyword with other WHERE clause conditions, provided those attributes are query-indexed.

Delete Group Data in Cache with SQL Query [Deprecated]

NCache provides object queries similar to SQL that allow you to delete cache items based on specified criteria, making it useful for cache cleanup and logical data partition management. To support this, NCache extends standard SQL syntax with the special $Group$ keyword, which enables you to target and delete all cache items belonging to a specific group or set of groups. The delete operation is executed using ExecuteNonQuery, which processes the query entirely on the server to minimize network overhead and returns the number of cache items removed. To use this approach, cached objects must be query-indexed and associated with groups in advance.

Prerequisites

Before deleting group data in cache using the SQL Query, ensure that the following prerequisites are fulfilled:

Install the following NuGet packages in your .NET client application:
- Enterprise: Alachisoft.NCache.SDK
- Open Source: Alachisoft.NCache.Opensource.SDK
Include the following namespaces in your application:
The cache must be running.
Make sure that the data being added is serializable.
Custom classes/searchable attributes must be indexed as explained in Configuring Query Indexes.
For API details, refer to: ICache, ICacheReader, QueryCommand, ExecuteNonQuery, SearchService.

Add the following Maven dependencies for your Java client application in pom.xml file:

<dependency>
    <groupId>com.alachisoft.ncache</groupId>
    <!--for NCache Enterprise-->
    <artifactId>ncache-client</artifactId>
    <version>x.x.x</version>
</dependency>

Import the following packages in your Java client application:
- import com.alachisoft.ncache.client.*;
- import com.alachisoft.ncache.runtime.exceptions.*;
- import com.alachisoft.ncache.runtime.caching.*;
The cache must be running.
Make sure that the data being added is serializable.
Custom classes/searchable attributes must be indexed as explained in Configuring Query Indexes.
For API details, refer to: Cache, CacheReader, executeReader, QueryCommand, getSearchService.

Delete Group

Given certain criteria, you can remove groups present in the cache with SQL queries. The following example removes all the data against a certain group through the SQL delete query.

try
{
  // Precondition: Cache is already connected

  // A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
  // Custom class is query indexed through the NCache Management Center or config.ncconf
  // Create SQL Query with the specified criteria
  // Make sure to use the Fully Qualified Name (FQN)
  string query = "DELETE FROM FQN.Customer WHERE $Group$ = ?";

  // Use QueryCommand for query execution
  var queryCommand = new QueryCommand(query);

  // Providing parameters for query
  queryCommand.Parameters.Add("$Group$", "West Coast Customers");

  // 'rowsAffected' number of items removed from cache
  int rowsAffected = cache.SearchService.ExecuteNonQuery(queryCommand);

  if (rowsAffected == 0)
  {
      Console.WriteLine($"No West Coast Customers removed");
  }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures 
        // Operation Timeout
        // Operation performed during state transfer    
    }
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

try
{
  // Precondition: Cache is already connected

  // A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
  // Custom class is query indexed through the NCache Management Center or config.ncconf
  // Create SQL Query with the specified criteria
  // Make sure to use the Fully Qualified Name (FQN)
  String query = "DELETE FROM FQN.Customer WHERE $Group$ = ?";

  // Use QueryCommand for query execution
  QueryCommand queryCommand = new QueryCommand(query);

  // Providing parameters for query
  queryCommand.getParameters().put("$Group$", "West Coast Customers");

  // 'rowsAffected' number of items removed from cache
  int rowsAffected = cache.getSearchService().executeNonQuery(queryCommand);

  if (rowsAffected == 0)
  {
      System.out.println("No west coast customers removed");
  }
}
catch (OperationFailedException ex)
{
    if (ex.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures
        // Operation Timeout
        // Operation performed during state transfer
    }
}
catch (Exception ex)
{
    // Any generic exception like NullPointerException or IllegalArgumentException
}

try:
    # Precondition: Cache is already connected

    # A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
    # Custom class is query indexed through the NCache Management Center or config.ncconf
    # Create SQL Query with the specified criteria
    # Make sure to use the Fully Qualified Name (FQN)
    query = "DELETE FROM FQN.Customer WHERE $Group$ = ?"

    # Use QueryCommand for query execution
    query_command = QueryCommand(query)

    # Provide parameters for the query
    query_command.set_parameters({"$Group$": "West Coast Customers"})

    # 'rowsAffected' number of items removed from cache
    search_service = cache.get_search_service()
    rows_affected = search_service.execute_non_query(query_command)

    if rows_affected == 0:
        print("No West Coast Customers removed")

except Exception as error:
    # Exception can occur due to:
    # Connection Failures
    # Operation Timeout
    # Operation during state transfer
    print("An error occurred:", str(error))

try
{
  // Precondition: Cache is already connected
  // This is an async method

  // A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
  // Custom class is query indexed through the NCache Management Center or config.ncconf
  // Create SQL Query with the specified criteria
  // Make sure to use the Fully Qualified Name (FQN)
  const query = "DELETE FROM FQN.Customer WHERE $Group$ = ?";

  // Use QueryCommand for query execution
  const queryCommand = new QueryCommand(query);

  // Providing parameters for query
  const parameters = new Map();
  parameters.set("$Group$", "West Coast Customers");
  queryCommand.setParameters(parameters);

  // 'rowsAffected' number of items removed from cache
  const searchService = await cache.getSearchService();
  const rowsAffected = await searchService.executeNonQuery(queryCommand);

  if (rowsAffected === 0) 
  {
      console.log("No West Coast Customers removed");
  }
}
catch(error)
{
   // Handle errors
}

Note

To ensure the operation is fail-safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

For more details on object queries, please refer to the SQL Reference for NCache section.

Additional Resources

NCache provides sample application for Groups on GitHub.

Delete Group Data in Cache with SQL Query [Deprecated]

Prerequisites

Delete Group

Note

Additional Resources

See Also

Contact Us