Cold-cache N+1: DbFieldTransformer eagerly loads field variables and double-transforms each field

[swicken]

Problem Statement

On a cold cache, hydrating content fires a large N+1 burst of field-variable queries:

select id, field_id, variable_name, variable_key, variable_value, user_id, last_mod_date
from field_variable where field_id = ?

A single cold POST /api/content/_search for Blogs with render:true on the demo (Costa Rica) starter issues ~298 of these. The render path never actually reads field variables (warm is already 0), so every one of these cold queries is wasted work.

Root cause — com.dotcms.contenttype.transform.field.DbFieldTransformer has two defects in the field-loading path used by FieldFactoryImpl.selectByContentTypeInDb(...) → ContentType.fields():

1. Eager hydration: fromMap(...) force-calls newField.fieldVariables() on every field as it is transformed from the DB. Field#fieldVariables() is a @Value.Lazy getter that loads variables via FieldFactoryImpl.loadVariables(...) (raw field_variable SELECT). Forcing it loads variables for every field even when nothing in the consumer ever reads them.
2. Double transform: asList() runs fromMap(map) twice per field — the first result is assigned to an unused local Field f (dead code) and discarded; the second is added to the list. This doubles the per-field load (the line was clearly meant to be list.add(f)).

Combined, these turn one field-set load into 2 × fieldCount field_variable queries. This is why the rejected side-cache spike landed at exactly 149 (≈ 298 / 2): it was masking the double-call, not addressing the eager load.

Impact: every cold content hydration across the platform (content search, page render, API) pays a redundant per-field field_variable query burst — unnecessary DB load and cold-start latency on a hot, widely-used legacy path. Functionally correct (content renders), purely an efficiency defect.

Cold Blog _search (render:true), demo starter	Released baseline	This fix
field_variable queries	298	0
total JDBC	632	374
warm field_variable / JDBC	0 / 46	0 / 46

Steps to Reproduce

Measured with the local OpenTelemetry → Grafana Tempo rig (JDBC spans per request).

1. Start dotCMS on the demo (Costa Rica) starter.
2. Cold: restart the dotCMS container to clear in-memory caches (data persists).
3. Fire one request:

   curl -s -o /dev/null -X POST http://localhost:8080/api/content/_search \
     -H 'Content-Type: application/json' \
     -d '{"query":"+contentType:Blog +live:true","limit":10,"render":true}'

4. Count field_variable JDBC spans in the resulting trace → ~298 on a cold cache.
5. Repeat the search (warm) → 0 field_variable queries (the existing Field/ContentType cache absorbs it).

The cold count scales with 2 × (fields per distinct content type touched), confirming the double-transform + eager-load behavior in DbFieldTransformer.

Acceptance Criteria

• A cold POST /api/content/_search with render:true on the demo Blogs issues 0 field_variable queries (down from ~298); warm remains 0.
• Field variables are loaded lazily — only when a field's fieldVariables() is actually read — and are served from the existing Field/ContentType cache thereafter (no new side-cache introduced).
• DbFieldTransformer.asList() transforms each field exactly once (no double fromMap).
• Field-variable reads remain correct: adding, editing, or deleting a field variable is reflected on the next read (no staleness); behavior under transaction rollback and in a cluster is unchanged.
• An integration test asserts: loading a content type's fields triggers 0 eager field_variable loads, lazy access loads exactly once per field, and warm reads are served from cache.
• No regression in existing field / content-type behavior (FieldFactoryImplTest and related suites pass).

dotCMS Version

Reproducible on the latest main and on released 26.04.22-02. Longstanding — the eager fieldVariables() call and the asList() double-transform predate 2023 (the 2023 OpenRewrite commit only reformatted the latter).

Severity

Medium - Some functionality impacted

(No functional breakage — content renders correctly — but a platform-wide cold-cache N+1 causing redundant DB load and cold-start latency on a hot legacy path.)

Links

NA

[github-actions]

PRs linked to this issue

• fix(content-types): lazy-load field variables to eliminate cold-cache N+1 hydration by @swicken