Pivotal Knowledge Base

Follow

How to run queries with ORDER and LIMIT in functions

Environment

Product Version
Pivotal GemFire All versions

Purpose

This article describes how to implement queries using ORDER and LIMIT that are executed using data-dependent functions.

Description

If the query is to be run on regions that are not co-located, it is recommended to not use functions to execute the query. In this scenario, it is simpler to use the QueryService Java API as this will return the expected results in one ArrayList as described in the Users Guide.

If the data is to be joined from different co-located regions, the query should be run in a data-dependent function.

When queries are run in a data-dependent function, the coordinating member receives results from the other members and sends the results to the requesting member that executed the function. Note that the configuration parameter pr-single-hop-enabled (which is true by default), changes how functions are executed. When single-hop is enabled, the client will act as the coordinating member and results will be sent directly from the members executing the function to the client.

However, the executor obtains an ArrayList with result arrays from each of the different members. Hence, when ORDER or LIMIT is used in the query, you will need to implement a custom ResultsCollector to handle the results if you want the function executor to receive the expected results in a single ordered, and limited, result set.

If, for example, the following query is executed using a function on three members:

select distinct * from /ShipmentsView order by createDate limit 10

the function executor will receive an ArrayList that contains 3 ArrayLists, one from each member. Moreover, each of the three ArrayLists will contain ten or fewer hits. Since the results are in three separate ArrayLists, each is ordered itself.

The recommended method to resolve this is to implement a custom ResultsCollector, so that the function coordinator will handle the results correctly and send the expected results back to the requester (see How to implement a custom ResultsCollector).

Comments

Powered by Zendesk