Optimizing Queries on Data Partitioned by a Key or Field Value

This topic explains how to optimize queries on data partitioned by a key or field value in VMware Tanzu GemFire.

You can improve query performance on data that is partitioned by a 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 example shows how to optimize a query that will 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. For more information about creating key indexes, see Creating Key Indexes.

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);
       }
     }
   }