Slow contentlet save with required relationships — unbounded query loop in validateRelationships()

[erickgonzalez]

Problem Statement

Saving a contentlet that has multiple required many-to-many relationship fields takes minutes (up to ~10 min) instead of seconds when the related content volume and configured language count are non-trivial. Slowness is directly proportional to (related identifiers) × (configured languages).

A previous patch for #34454 optimized tree-table queries in RelationshipFactoryImpl, but profiling shows the bottleneck has shifted to a different code path — ESContentletAPIImpl.validateRelationships() — which still runs an unbounded existence-check loop. After that patch, ~99% of CPU time during a slow save is still concentrated in this method (623 of 627 profiler samples on an affected environment).

Symptoms (representative environment with several hundred related records and ~40+ languages):

Scenario	Save time
New contentlet, no relationships	1–3 s (normal)
New contentlet, ~3 related records	~40 s
New contentlet, ~10 related records	2+ min
Re-save existing contentlet with full relationships	~10 min

Root Cause

ESContentletAPIImpl.validateRelationships() calls getRelatedContent() to verify required relationships when they are not in the save payload. That method:

1. Loads all related identifiers from RelationshipCache.
2. For each identifier, loops over every configured language and calls findContentletByIdentifier(identifier, live, language).

With ~500 related identifiers and ~40 languages this fires ~20,000+ SQL queries per save (contentlet_version_info, contentlet, identifier), even though the result is only consumed as a boolean !isEmpty() existence check.

Proposed Fix

File: dotCMS/src/main/java/com/dotcms/content/elasticsearch/business/ESContentletAPIImpl.java
Method: validateRelationships()

Replace the unbounded load:

final List<Contentlet> existingRelatedContent = getRelatedContent(
    contentlet, rel, checkParent, APILocator.systemUser(), false);
hasExistingRelatedContent = existingRelatedContent != null
    && !existingRelatedContent.isEmpty();

with a bounded existence check:

hasExistingRelatedContent = !FactoryLocator.getRelationshipFactory()
    .dbRelatedContent(rel, contentlet, checkParent, false, null, 1, 0)
    .isEmpty();

dbRelatedContent(..., 1, 0) issues a single LIMIT 1 query against tree + contentlet_version_info and stops as soon as one record is found. The result has the same boolean meaning as before. Permission filtering is skipped, but the original call already ran as systemUser so all content passed permission checks anyway.

Steps to Reproduce

1. Run dotCMS 24.12.27 LTS (or newer) with the patch for [TASK] Optimize heavy SQL queries #34454 applied.
2. Create content type ParentType with three required many-to-many relationship fields to ChildA, ChildB, ChildC.
3. Configure 30+ languages in the system.
4. Populate ~500 records on ChildA, smaller volumes on ChildB/ChildC.
5. Create one ParentType contentlet linked to a meaningful subset of children, save it.
6. Open it again and re-save without changing any field. Capture the trace in Glowroot.

Expected: Save completes in a few seconds; JDBC query count is in the hundreds.
Actual: Save takes minutes; JDBC query count exceeds 20,000; contentlet_version_info SELECT executes tens of thousands of times.

Acceptance Criteria

• Re-saving an existing contentlet with required relationships completes in under 10 seconds (regression baseline: ~10 minutes).
• Total JDBC executions during the save are in the hundreds, not tens of thousands; contentlet_version_info query count stays under ~100.
• Save scaling is roughly proportional to direct relationship size — saving with 20 children is not 10× slower than saving with 3 children.
• Required-relationship validation still rejects saves where a required relationship has no children and none exist in the DB.
• Required-relationship validation still passes when the relationship is omitted from the save payload but children exist in the DB.
• Content types with no required relationships are unaffected (no behavioral or performance change).
• ONE_TO_ONE cardinality validation still rejects saves that link more than one contentlet on a one-to-one field.

dotCMS Version

24.12.27 LTS (with the patch for #34454 applied). Issue likely affects main and other LTS branches that share the same validateRelationships() code path. Backport to 24.12.27 LTS is required.

Severity

High - Major functionality broken

Links

• Related: [TASK] Optimize heavy SQL queries #34454 (prior optimization in RelationshipFactoryImpl, addressed a different query)
• Freshdesk #36004

[github-actions]

PRs linked to this issue

• fix(content): replace unbounded query loop with LIMIT-1 DB call in validateRelationships() (#35489) by @fabrizzio-dotCMS