Optimize Repository Queries with Date Range Constraints for Performance

Overview

This page explains how to improve the performance of repository queries that use date range constraints. By specifying an appropriate date resolution, you can avoid slow queries and potential out-of-memory errors in the underlying Jackrabbit repository.

Problem Statement

Jackrabbit, which serves as the foundation for the Hippo Repository, exhibits significant performance issues when executing date range queries—queries that compare properties of type Date. These issues can occur even with a moderate number of documents. For more background, see Make your date range queries in Jackrabbit go faster and derived data function.

This document focuses on optimizing direct repository queries with date ranges. For optimizing queries in the delivery tier API, refer to Optimize Delivery Tier API Queries with Date Range Constraints for Performance.

Date Range Constraints in Repository Queries

A typical XPath query with a date range constraint looks like this:

 //element(*,custom:document)
           [@custom:date >=xs:dateTime('2009-01-01T03:23:54.234Z') and
            @custom:date <=xs:dateTime('2013-01-01T06:41:30.056Z')]
            order by @custom:date descending

The xs:dateTime(...) fragment is generated from a Calendar instance as follows:

final Calendar calendar = ...;
String xsDateTimeFormat = session.getValueFactory().
                     createValue(calendar).getString(); 
copy

This approach creates range queries with millisecond precision. Such queries are slow and can cause out-of-memory errors. Always use one of the supported resolutions described below to avoid these issues.

Creating Fast and Scalable Date Range Queries

In most cases, day, month, or year resolution is sufficient for date range queries in content management scenarios. For example, users may want to find documents published between two dates or documents modified in a specific year.

The Hippo Repository indexes all Calendar JCR properties at multiple resolutions to support efficient date range queries. The supported resolutions are:

• year
• month
• day
• hour

The utility class DateTools in the Hippo Repository API provides helper methods for building efficient XPath date range constraints:

String DateTools#getPropertyForResolution(String property,
                  org.hippoecm.repository.util.DateTools.Resolution resolution) 
copy

and

String DateTools#createXPathConstraint(javax.jcr.Session session,
                  java.util.Calendar calendar,
                  org.hippoecm.repository.util.DateTools.Resolution roundDateBy)
copy

• getPropertyForResolution returns the name of the indexed property for the specified resolution.
• createXPathConstraint generates an XPath fragment for the given date, rounded to the specified resolution.

Example: Date Range Query with "Day" Resolution

To rewrite an XPath query for better performance using day resolution:

Original query:

 //element(*,custom:document)
                 [@custom:date >=xs:dateTime('2009-01-01T04:04:56.456Z')]
                  order by @custom:date descending

Optimized query construction:

Calendar calendar = ...; // 2009-01-01T34:04:56.456Z
// Get the property for 'day' resolution
String xpathProperty = DateTools.getPropertyForResolution("custom:date",
                                        DateTools.Resolution.DAY);
// Create the XPath constraint for 'day' resolution
String xpathDate = DateTools.createXPathConstraint(session,
                                    calendar ,
                                    DateTools.Resolution.DAY);
String xpath = "//element(*,custom:document)
               [@" +xpathProperty+ " >= " + xpathDate + "]
               order by @custom:date descending";
copy

The resulting XPath query:

 //element(*,custom:document)
                 [@custom:date____day >=xs:dateTime('2009-01-01T00:00:00.000Z')]
                  order by @custom:date descending

Key points:

1. The query uses the property @custom:date____day for the range constraint.
2. The date value is rounded to the start of the day (T00:00:00.000Z).
3. Sorting is still performed on the original @custom:date property for exact ordering.

This approach enables fast execution and scales to millions of documents. Coarser resolutions (such as year) further improve performance. Note that using a coarser resolution may return more results than a query with millisecond precision, as the range is broader.

For example:

@custom:date >=xs:dateTime('2009-01-01T04:04:56.456Z')

becomes

@custom:date____day >=xs:dateTime('2009-01-01T00:00:00.000Z')

A document with custom:date = 2009-01-01T03:04:56.456Z matches the day-based query but not the original millisecond-based query.

Querying by Specific Year, Month, Day, or Hour

You can also use this approach to query for documents within a specific year, month, day, or hour. For example:

1. All documents last modified in 2013
2. All documents published in February 2013
3. All documents modified on 2013-02-03

For these cases, use an equality comparison instead of a range. For example, to find all documents from 2013:

Calendar any2013Date = Calendar.getInstance();
any2013Date.set(Calendar.YEAR, 2013);

// Get the property for 'year' resolution
String xpathProperty = DateTools.getPropertyForResolution("custom:date",
                                        DateTools.Resolution.YEAR);
// Create the XPath constraint for 'year' resolution
String xpathDate = DateTools.createXPathConstraint(session,
                                    any2013Date,
                                    DateTools.Resolution.YEAR);
// Use equality comparison
String xpath = "//element(*,custom:document)
               [@" +xpathProperty+ " = " + xpathDate + "]; 
copy

Technical Details: Date Range Query Performance in Jackrabbit

Jackrabbit indexes date properties in Lucene using their exact millisecond values. For properties like creationDate, lastModifiedDate, or publicationDate, each document typically has a unique timestamp.

When you execute a range query in Lucene, it expands into a Boolean query that ORs all unique values in the range. If you have 100,000 documents, each with a unique timestamp, a range query on creationDate results in a query with 100,000 OR terms. This is highly inefficient and can cause severe performance problems.

The Hippo Repository addresses this by indexing rounded timestamps at multiple resolutions. This enables fast, scalable date range queries.

Lucene 2.9 and later provides NumericRangeQuery for efficient range queries on numeric fields, including dates. However, Jackrabbit does not natively support this feature. Even with NumericRangeQuery, the performance is not as high as the resolution-based approach used in the Hippo Repository.

Summary

• Use date range queries with year, month, day, or hour resolution for optimal performance.
• Use the DateTools utility class to construct efficient XPath constraints.
• Avoid millisecond-precision range queries to prevent slow performance and memory issues.
• For queries targeting a specific year, month, day, or hour, use equality comparisons on the corresponding indexed property.

For further details, refer to the DateTools JavaDoc.