LATEST VERSION: 9.2.0 - CHANGELOG
Pivotal GemFire® v9.2

Optimizing Queries on Data Partitioned by a Key or Field Value

You can improve query performance on data that is partitioned by key or a field value by creating a key index and then executing the query using the FunctionService with the key or field value used as filter.

The following is an example how to optimize a query that will be run on data partitioned by region key value. In the following example, data is partitioned by the “orderId” field.

  1. Create a key index on the orderId field. See Creating Key Indexes for more details.
  2. Execute the query using the function service with orderId provided as the filter to the function context. For example:

    /**
     * Execute MyFunction for query on data partitioned by orderId key
     *
     */
    public class TestFunctionQuery {
    
      public static void main(String[] args) {
    
        Set filter =  new HashSet();
        ResultCollector rcollector = null;
    
        //Filter data based on orderId  = '12345'
        filter.add(12345);
    
        //Query to get all orders that match ID 12345 and amount > 1000
        String qStr = "SELECT * FROM /Orders WHERE orderId = '12345' AND amount > 1000";
    
        try {
          Function func = new MyFunction("testFunction");
    
          Region region = CacheFactory.getAnyInstance().getRegion("myPartitionRegion");
    
          //Function will be routed to one node containing the bucket
          //for ID=1 and query will execute on that bucket.
          rcollector = FunctionService
              .onRegion(region)
              .setArguments(qStr)
              .withFilter(filter)
              .execute(func);
    
          Object result = rcollector.getResult();
    
          //Results from one or multiple nodes.
          ArrayList resultList = (ArrayList)result;
    
          List queryResults = new ArrayList();
    
          if (resultList.size()!=0) {
            for (Object obj: resultList) {
              if (obj != null) {
                queryResults.addAll((ArrayList)obj);
              }
            }
          }
          printResults(queryResults);
    
        } catch (FunctionException ex) {
            getLogger().info(ex);
        }
      }
    }