DAM asset query performance issues caused by traversal warnings and inefficient index usage
Adobe Experience Manager (AEM) DAM asset queries become slow, time out, or fail when large result sets, unsupported query syntax, inefficient index selection, or insufficient query filters cause excessive node traversal. The issue affects Query Builder queries, SQL2 queries, scheduler jobs, API endpoints, and DAM asset search performance. To resolve the issue, optimize queries, improve indexing, and reduce traversal.
Description description
Environment
Adobe Experience Manager as a Cloud Service (AEMaaCS)
Issue/Symptoms
- Queries time out or return*“Index traversed too many nodes”* warnings.
- SQL2 queries fail with parse errors because Oak transforms unsupported LIMIT syntax. For example,
LIMIT 1becomesLIMIT(*)1, resulting in a ParseException. - Query Builder endpoints return Service Outage responses when large DAM paths trigger heavy traversal.
- Explain Query output shows no index usage and displays Indexes used: none.
- Queries against large DAM paths such as
/content/dam/...take hundreds of seconds or hang whenp.limit=-1is used.
Root cause
The issue occurs because large DAM queries without adequate filters force Oak to scan excessive numbers of nodes. Unsupported SQL2 LIMIT/OFFSET syntax, ineffective index selection, unsupported conditions such as IS NULL, and unbounded result sets prevent efficient index usage. Large folder hierarchies can also cause HTTP timeouts, resource exhaustion, and traversal warnings when queries are not optimized.
Resolution resolution
Follow these steps to identify the query issue and improve DAM query performance:
- Identify the query failure type by reviewing logs, Query Builder Debugger results, Query Performance output, and Explain Query results.
- Reduce large result sets by replacing
p.limit=-1with a reasonable limit and reducing the query scope when querying large DAM hierarchies. - Replace unsupported SQL2 LIMIT/OFFSET syntax with supported syntax such as
OPTION(LIMIT 1)and use Query Builder pagination when required. - Review Explain Query output and verify that the query uses the expected DAM index instead of fallback indexes.
- Enforce the appropriate index tag when Explain Query shows incorrect index selection. For example, add
option(index tag assetsOmnisearch)when required. - Remove or restructure IS NULL conditions that prevent index usage and force traversal.
- Split queries into smaller subqueries when the DAM hierarchy contains very large folder structures. For example, divide queries by subfolder instead of querying a single path containing tens of thousands of assets.
- Add additional metadata filters such as date, resource type, or other indexed properties to reduce the number of scanned nodes and improve index eligibility.
- Validate the resolution by rerunning the query, reviewing Explain Query output, confirming correct index usage, and ensuring traversal warnings and timeouts no longer occur.
When to escalate
- Traversal warnings continue after query limits and filters are applied.
- Explain Query continues to show no index usage after query optimization.
- Supported SQL2 syntax still fails after the query is corrected.
- Query Builder endpoints continue returning Service Outage errors after optimization and index validation.