Optimize Delivery Tier API Queries with Date Range Constraints for Performance

Introduction

Goal

Avoid slow date range queries through the Delivery Tier API by specifying a date resolution.

Background

In order to optimize performance, Hippo Repository provides different date resolutions for queries containing date range constraints. When building queries using Hippo's delivery tier API, a date resolution can be added to the date range constraint.

Specify a Date Resolution for a Query Constraint

When developing an application with Hippo's delivery tier, you can make use of fast date range query support through the Fluent Search API as well as the Legacy Search API.

Fluent Search API

A date resolution can be specified by passing an org.hippoecm.repository.util.DateTools.Resolution object into the following methods of org.hippoecm.hst.content.beans.query.builder.FieldConstraintBuilder:

• equalTo(Calendar, Resolution)
• notEqualTo(Calendar, Resolution)
• greaterOrEqualThan(Calendar, Resolution)
• greaterThan(Calendar, Resolution)
• lessOrEqualThan(Calendar, Resolution)
• lessThan(Calendar, Resolution)
• between(Calendar, Calendar, Resolution)
• notBetween(Calendar, Calendar, Resolution)

Example:

HstQuery hstQuery = HstQueryBuilder.create(scope)
        .ofTypes(BaseDocument.class)
        .where(constraint("myhippoproject:date")
                .between(oneMonthAgo, today, DateTools.Resolution.DAY))
        .build();

Legacy Search API

A date resolution can be specified by passing an org.hippoecm.repository.util.DateTools.Resolution object into the following methods of org.hippoecm.hst.content.beans.query.filter.Filter:

• addBetween(String, Calendar, Calendar, Resolution)
• addNotBetween(String, Calendar, Calendar, Resolution)
• addEqualTo(String, Calendar, Resolution)
• addNotEqualTo(String, Calendar, Resolution)
• addGreaterOrEqualThan(String, Calendar, Resolution)
• addGreaterThan(String, Calendar, Resolution)
• addLessOrEqualThan(String, Calendar, Resolution)
• addLessThan(String, Calendar, Resolution)

Example:

HstQuery hstQuery = context.getQueryManager().createQuery(scope, BaseDocument.class, true);
Filter filter = hstQuery.createFilter();
filter.addBetween("myhippoproject:date", oneMonthAgo, today, DateTools.Resolution.DAY);
hstQuery.setFilter(filter);

Date Resolution Values

The following date resolutions are defined in org.hippoecm.repository.util.DateTools.Resolution:

• DateTools.Resolution.YEAR
• DateTools.Resolution.MONTH
• DateTools.Resolution.DAY
• DateTools.Resolution.HOUR

The more coarse the resolution, the better the performance.

Default Resolution

By default, a resolution of DateTools.Resolution.DAY is used for fast date range queries. For example, when using Filter#addBetween(String, Object, Object) where the first Object is the Calendar start value and the second Object is the Calendar end value, the default resolution DateTools.Resolution.DAY is used.

You can change the default date resolution using the default.query.date.range.resolution property in the hst-config.properties file.

Supported values are 

1. '' (empty string. Like this, Jackrabbit's default resolution of milliseconds is used)
2. year
3. month
4. day
5. hour

It is not recommended to set the default resolution to '' (empty string) because this can result in poor query performance and even out-of-memory conditions.