JCR query optimisation: how to stop killing your AEM performance

The AEM performance issue that nobody talks about until it’s too late: JCR queries running without proper Oak indexes, doing full repository traversals on every request. I’ve seen publish tiers brought to their knees by a single unindexed query hitting every node under /content. Here’s how to fix it before it hits production.

XPath vs SQL-2: which one to use

Both work. SQL-2 is the modern standard and better supported by Oak’s query engine. XPath is legacy but still widely used in older AEM codebases. The performance difference between them is negligible — what matters is whether the query is backed by an Oak index.

// SQL-2 — preferred for new development
String query = "SELECT * FROM [cq:Page] AS page "
    + "WHERE ISDESCENDANTNODE(page, '/content/mysite') "
    + "AND [jcr:content/sling:resourceType] = 'mysite/components/article' "
    + "ORDER BY [jcr:content/jcr:lastModified] DESC";

// XPath equivalent — still valid, just older syntax
String xpath = "/jcr:root/content/mysite//element(*, cq:Page)"
    + "[@jcr:content/@sling:resourceType = 'mysite/components/article'] "
    + "order by @jcr:content/@jcr:lastModified descending";

The Oak index: what you must configure

Every property you query against must have a corresponding Oak index entry or the query engine falls back to full traversal. Full traversal on a repository with 100k+ nodes will kill your response times. Configure a lucene index for your custom properties:

// Oak index definition (as JCR node structure)
oak:index/articleIndex {
  jcr:primaryType: oak:QueryIndexDefinition
  type: lucene
  async: async
  includedPaths: ['/content/mysite']
  indexRules {
    cq:Page {
      properties {
        resourceType {
          name: jcr:content/sling:resourceType
          propertyIndex: true
        }
        lastModified {
          name: jcr:content/jcr:lastModified
          type: Date
          ordered: true    // required for ORDER BY to use index
        }
      }
    }
  }
}

Diagnosing slow queries

AEM has built-in query performance tools. Use the Query Performance tab in CRXDE Lite (/crx/de → Tools → Query Performance) or the Oak Query Explain API to see whether your query is using an index or traversing:

// Programmatic query explanation via Sling Model
@Reference
private QueryEngine queryEngine;

public String explainQuery(String sql2Query, Session session) {
    QueryResult result = queryEngine.executeQuery(
        sql2Query, Query.JCR_SQL2, session, null
    );
    // Check result.getQueryStatement() for Oak's execution plan
    return result.getQueryStatement();
}

If the explain output shows “traversal” instead of an index name, you need an index. If it shows an index but query time is still slow, check whether the index needs reindexing after recent content growth.

The patterns that will kill your publish tier

Pattern 1: Query inside a loop. Never execute a JCR query inside a loop that iterates over results from another query. This is the N+1 problem applied to content repositories. Restructure to fetch all data in a single query with proper predicates.

Pattern 2: Unbounded result sets. Always set a limit on your queries. A query without a limit can return thousands of nodes on a large repository. Use setLimit() on QueryManager or the LIMIT clause in SQL-2.

Pattern 3: Querying in Sling filters or event handlers. These execute on every request or every content change respectively. A slow query here cascades into systemic performance degradation.

Index your queries. Explain your queries in staging before production. Set limits. These three habits will keep your AEM publish tier healthy under load.