OData large query throttling

Hybrid Data Pipeline supports throttling large OData queries against data sources. Large queries are defined here as queries that require paging in order to return results.

By default, when executing an OData query, Hybrid Data Pipeline sends the query to the backend data store. All the results are then fetched and persisted, and the first page of results is returned to the application. Having multiple large queries running simultaneously can negatively impact the performance of the service for all users. Furthermore, some results are never fully viewed by applications, meaning that resources are unnecessarily allocated to return unused data. As a result, an administrator may want to limit the number of large OData queries.

OData large query throttling may be configured with the ODataMaxConcurrentPagingQueries limit. When the ODataMaxConcurrentPagingQueries limit is set to 0 (zero), there is no maximum number of large queries against the data source. When ODataMaxConcurrentPagingQueries is set to a positive integer, rows are fetched one page in advance of application requests. This maintains quick response times in addition to minimizing the expense associated with executing large queries. Queries that contain more than one page of results are persisted in system memory until completely returned to the application or terminated. To prevent users from exhausting system and database resources, the maximum number of large queries are limited to the value specified. When this limit is exceeded, the least recently used large query is canceled, and subsequent attempts to retrieve data from the canceled query will fail.

The ODataMaxConcurrentPagingQueries limit can be applied at four levels in the following manner.

• Data source. When applied to a data source, the limit applies only to the data source. A limit applied at the data source level overrides the limit set at the other levels.
• User. When applied to a user account, the limit applies to the data sources owned by that user. A limit applied at the user level overrides limits set at the tenant and system levels.
• Tenant. When applied to a tenant, the limit applies to all data sources in the tenant. A limit applied at the tenant level overrides a limit set at the system level.
• System. When applied at the system level, the limit applies to all data sources in the Hybrid Data Pipeline system.

For example, with an ODataMaxConcurrentPagingQueries limit of 10 set on at the system level, 500 concurrent users would be able to have 10 queries for each data source that they own.

To configure OData large query throttling, the administrator must have either the Administrator (12) or the Limits (27) permission.

POST https://myserver:port/api/admin/limits/system/6

    {
      "value": 50
    }              

POST https://<myserver>:<port>/api/admin/limits/tenants/32/6

    {
      "value": 60
    }              

POST https://<myserver>:<port>/api/admin/limits/users/86/6

    {
      "value": 30
    }              

PUT https://<myserver>:<port>/api/admin/limits/users/86/datasources/14/6

    {
      "value": 100
    }