LocalRegistry.get() on a 60k-node Hierarchy never returns: _resolve_refs fans out N+1 SQL queries

Summary

LocalRegistry.get(hierarchy_urn) for a Hierarchy containing 60,000+ HierarchicalCode nodes never returns in practice — the post-fetch _resolve_refs walk visits every node's code: Ref[Code] field and issues one SQL query per Ref. With 60k codes that's tens of thousands of probes; the call hangs at 100% CPU indefinitely.

Bypassing _resolve_refs (calling readers.get_artefact(cursor, urn, eager=False) directly) returns the same Hierarchy in 11.4s.

Repro

import time, sdmxlib as sl
from sdmxlib.storage import readers

# Build a Hierarchy with N HierarchicalCodes, each with code=SdmxUrn[Code](item_urn)
# Persist it. Then:

# Slow path (hangs for N=60_000):
with sl.LocalRegistry(db) as reg:
    t0 = time.monotonic()
    h = reg.get(sl.SdmxUrn.parse(hierarchy_urn))   # never returns
    print(f"get(): {time.monotonic()-t0:.2f}s")

# Bypass:
with sl.LocalRegistry(db) as reg:
    t0 = time.monotonic()
    h = readers.get_artefact(reg._cursor, hierarchy_urn, eager=False)  # ~11s
    print(f"raw: {time.monotonic()-t0:.2f}s")

In our case (cenprof-ai's H_CP_GEO_STATISTICAL, 60,170 nodes — every node has a code Ref pointing into CL_GEOGRAPHY):

  • reg.get(urn): still running at 4+ minutes, killed.
  • readers.get_artefact(...): 11.4 s.

Root cause

LocalRegistry.get() (local/_registry.py:170-175):

result = readers.get_artefact(self._cursor, str(urn), eager=eager)
if result is None: return None
if not isinstance(result, LazyCodelist):
    self._resolve_refs(result, set())
return result

_resolve_refs walks every attrs field on the artefact recursively and resolves any Ref field by hitting DuckDB. A Hierarchy.tree is a deep nested structure of HierarchicalCodes; each HierarchicalCode.code is a Ref[Code]. So get(hierarchy_urn) triggers one SQL roundtrip per node — exactly the N+1 antipattern that the lazy-codelist path was specifically designed to avoid.

Suggested fixes (any of these would unblock)

  1. Skip Ref resolution inside HierarchicalCode trees — the Hierarchy's structural data (Levels, parent/child, ancestor_ids) is already self-contained in hierarchy_node. The code: Ref[Code] field is supplementary; consumers who need the resolved Code can fetch it themselves. Treat HierarchicalCode like LazyCodelist: don't deep-resolve.

  2. Batch Ref resolution. Walk the tree once collecting all Refs, run a single WHERE urn IN (?,?,…) query, then assign results back. One SQL roundtrip instead of N.

  3. Add an eager=False flag that skips _resolve_refs entirely. Already exists for codelist materialization; extend to Hierarchy too. Then reg.get(urn, eager=False) returns the Hierarchy with unresolved Refs, the same shape readers.get_artefact(...) already gives back.

For our use case, (1) or (3) work — we never call node.code after read; the Hierarchy's own level and children data is what we need.

Workaround

Bypass via the readers layer:

from sdmxlib.storage import readers
h = readers.get_artefact(reg._cursor, hierarchy_urn, eager=False)

Costs 11s for 60k nodes (still slow but acceptable when cached at module level for the lifetime of the process). We're using this in cenprof-ai today.

Environment

  • sdmxlib 0.20.1
  • DuckDB
  • Python 3.13
  • macOS