perf(readers): _read_concept_scheme issues N+1 SQL for per-concept annotations

Summary

_read_concept_scheme (readers.py:451) issues one _read_annotations SQL query per concept in the scheme. A 331-concept scheme costs ~1.1 s and ~332 DuckDB executes for a 405 KB response that should be sub-50 ms. Same pattern applies to _read_codelist_eager (line 152) and to _read_data_structure (line 393, per-component annotation reads).

Independent of #72 (closed) — even after _resolve_refs is fixed not to re-read the same scheme N times, a single _read_concept_scheme call would still be O(N concepts) round-trips against DuckDB.

Repro

import time, duckdb
from sdmxlib.storage.readers import _read_concept_scheme

conn = duckdb.connect("/path/to/meta.duckdb", read_only=True)
conn.execute("LOAD ducklake")  # if applicable
conn.execute("SET search_path = 'sdmx'")

scheme_urn = "urn:sdmx:org.sdmx.infomodel.conceptscheme.ConceptScheme=STC_CENSUS:CS_CP(1.0)"
_read_concept_scheme(conn, scheme_urn)  # prime caches
t = time.perf_counter()
_read_concept_scheme(conn, scheme_urn)
print(f"{(time.perf_counter()-t)*1000:.0f} ms")  # ~1100 ms on a 331-concept scheme

Profile evidence

cProfile of Registry.get(<DSD URN>) in cpt-sdmx-api against the cenprof-2021 bundle (DSD has 20 components, all referencing concepts in STC_CENSUS:CS_CP(1.0) which carries 331 concepts):

       ncalls  tottime  cumtime  filename:lineno(function)
           20    0.051   34.446  readers.py:451(_read_concept_scheme)
         6661    0.390   33.989  readers.py:98(_read_annotations)
        35111   20.609   32.225  {built-in method _duckdb.execute}

_read_annotations is called 6,661 times = 331 concepts × 20 scheme re-reads (#72 (closed)) + 21 top-level annotation reads. Even with the 20× multiplier from #72 (closed) removed, a single _read_concept_scheme call still produces 331 _read_annotations calls = 331 + 331×k DuckDB executes (k = avg annotations per concept).

Root cause

_read_concept_scheme loops over the concept rows and calls _read_annotations(conn, concept_urn) per concept (readers.py:498-ish). _read_annotations itself does 1+N queries: 1 for annotation rows + N for annotation_text rows.

For a scheme with 331 concepts each carrying 0-2 annotations, that's 330-990 SQL round-trips per _read_concept_scheme call.

Same shape in:

  • _read_codelist_eager (readers.py:152) — per-code annotation reads
  • _read_data_structure (readers.py:393) — per-component annotation reads (minor: 5-50 components, but compounds with #72 (closed))

Suggested fix

Replace per-item _read_annotations with one bulk join scoped to the parent artefact. Sketch for _read_concept_scheme:

SELECT a.owner_urn, a.id, a.annotation_id, a.title, a.annotation_type, a.url,
       at.lang, at.text
FROM annotation a
LEFT JOIN annotation_text at ON at.annotation_id = a.id
WHERE a.owner_urn IN (SELECT concept_urn FROM concept WHERE scheme_urn = ?)
ORDER BY a.owner_urn, a.ordinal

Group rows by owner_urn in Python and build the Annotations for each concept. One round-trip per scheme instead of N.

A more general helper would take (owner_urn_list) and return dict[urn, Annotations]; both _read_concept_scheme, _read_codelist_eager, and _read_data_structure (along with any future readers that iterate items) could call it.

Expected impact

  • ConceptScheme read: ~1.1 s → ~50 ms (on the 331-concept CS_CP example)
  • Codelist eager read: similar order of magnitude on large codelists
  • DSD read (combined with #72 (closed)): 22 s → ~50 ms

Environment

  • sdmxlib: current main
  • DuckDB: 1.5.x
  • Python 3.13 / 3.14
  • Bundle: cenprof-2021 (STC_CENSUS:CS_CP(1.0) → 331 concepts)

Related

  • #72 (closed) — _resolve_refs fan-out (compounds with this issue on DSD reads)
Edited by Gabriel Gellner