AgentSM: Semantic Memory for Agentic Text-to-SQL — L2 #

1. TL;DR #

AgentSM makes a Text-to-SQL agent reuse structured prior exploration traces (built offline from synthetic questions) and merges frequently co-occurring tool calls into composite tools. This cuts trajectory length ~25% and lifts accuracy to a SOTA 44.8% on Spider 2.0 Lite — without a heavy reasoning model.

2. Q1 / Q2 / Q3 #

Q1 — 痛点 (what breaks). Agentic Text-to-SQL scales badly on enterprise schemas (deep nesting, multi-dialect, thousands of columns). Three concrete failures, each grounded in observed agent behavior:

• Repeated exploration: agents re-run near-identical schema-probing on the same DB every query; prior work reports <10–20% of trajectories are distinct.
• Rigid strategy: a single fixed pipeline (e.g. always vector-search) is suboptimal — Fig 2 shows vector search is actually chosen only ~33% of the time.
• Variance: trivial intermediate errors derail reasoning; temperature-0 does not enforce determinism and would kill useful exploration anyway.

The formal objective is to find the trajectory maximizing accuracy: $\tau^{} = \arg\max_{\tau \in \mathcal{T}(q, D, \mathcal{U})} \text{Acc}(\tau)$ — i.e. optimize the whole tool-use + reasoning sequence*, not just the final single-shot SQL.

Q2 — 方法 (the mechanism). Two tightly-coupled ReAct coding agents (planner + schema-linking) sit over a structured semantic memory = a Trajectory Store. Offline, synthetic questions (Algorithm 1) are executed to harvest dense exploration traces; each trace is regex-classified into phases (explore / execute / validate), rewritten into markdown with LLM-generated headers, and stored per-phase. At inference, the store is filtered to the same database then the most semantically similar question's trajectory is loaded (Eqs. 1–3). Separately, composite tools merge tool subsequences that co-occur above a support threshold $\theta$ into one action (e.g. get_ext + get_ddl ⇒ local_exploration).

核心技术壁垒: the insight that *data exploration is reusable across questions on the same DB — and that this reuse can be manufactured offline via synthetic questions rather than waiting for real traffic*. This decouples memory quality from query volume and is what lets a mid-tier model beat reasoning-model systems.

Q3 — 结果 (outcome). 44.8% overall EX on Spider 2.0 Lite (claude-4-sonnet), +14.1 pts over SpiderAgent, >20 pts over a same-framework CodingAgent, and would rank No. 1 on the leaderboard at submission — achieved without Qwen3/o3. Trajectory length drops (median 20→16 steps); the exploration:execution split flips from 65:35 to 44:56. Gold-tables oracle reaches 57.6% (Table 2), exposing schema linking as the dominant remaining bottleneck.

3. 架构 / 方法图 #

Paper's Figure 3 ("An overview of the AgentSM architecture that leverages trajectories in structured semantic memory"). The planner agent owns the reasoning loop and generates/executes SQL in-line (deliberately not delegated to a sub-agent, to avoid context loss and inter-agent latency). It calls the schema-linking agent — a bounded (≈5-step) worker with the vector-search tool — only for deep table probing. The Trajectory Store on the left is populated from raw + synthetic questions, then step-classified and semantically annotated, feeding "Trajectory Reuse" back into the planner. Notice the reuse arrow ("Retrieve most similar question") is what closes the loop: prior exploration is injected as context instead of being re-derived.

Paper's Figure 1 — the baseline workflow AgentSM optimizes: Data Exploration → Query Generation → Validation & Output, with back-edges on "missing constraints" / "syntax error". This figure is load-bearing for the method because the three phases become the regex step-classification labels; reuse is grounded per-phase (exploration steps generalize across questions, execution steps are DB-specific).

The Mermaid adds the retrieval→reuse control flow that Fig 3's raster crowds together: filter, rank, load, then plan with composite tools.

4. 作者证明 #

无形式化作者证明 — 仅实证. AgentSM has no convergence theorem, variance bound, or loss decomposition; it is an empirical systems/algorithm paper. Its "guarantees" are heuristic constructions with definitional equations rather than proved statements. A desirable formal result would be a bound on exploration-step savings as a function of trajectory-store coverage, or a variance-reduction guarantee from composite-tool determinism — neither is provided.

Notation table:

方程物理意义:

• Objective $\tau^{*}=\arg\max_{\tau}\text{Acc}(\tau)$: optimize the sequence, motivating memory + tool composition over single-shot decoding.
• $\mathcal{C} = \{ q' \in Q_{syn} \mid \text{db}(q') = \text{db}(q) \}$ (Eq. 1): exploration only transfers within a schema, so retrieval is DB-scoped.
• $q^{*} = \arg\max_{q' \in \mathcal{C}} \text{sim}(q', q)$ (Eq. 2): semantically similar questions probe the same tables during initial exploration.
• $\tau = \text{traj}(q^{*})$ (Eq. 3): load that trajectory verbatim as reusable context.
• $c = \langle t_1,\ldots,t_k \rangle \ \text{if}\ \text{freq}(t_1,\ldots,t_k) \geq \theta$: merge a frequent, phase-coherent tool subsequence into one action.

Minimum checks (6):

1. Objective well-posed? Yes — argmax over a finite (per-run bounded) trajectory space; but $\text{Acc}(\tau)$ is only observable post-execution, so it is optimized greedily/heuristically, never solved exactly.
2. Eq. 1 soundness: DB-scoping is safe but can miss cross-DB-transferable generic exploration (e.g. reading a DDL file) — a recall cost the authors accept.
3. Eq. 2 single-pick risk: choosing only the top-1 $q^{*}$ is brittle if similarity is noisy; authors flag finer-grained (schema/plan-level) retrieval as future work, admitting top-1 is a heuristic.
4. Composite-tool threshold $\theta$: no value disclosed; construction depends on it plus three heuristics (same-phase only, exclude cross-phase tools, cap size). Sensitivity untested.
5. Step-classification via regex: claimed comparable accuracy to LLM classification at lower cost — asserted from "a sample," not quantified beyond Table 1's 20-question probe.
6. Internal consistency: several headline numbers disagree across sections (see §6) — the formalism is clean but the reported deltas are not fully reconciled.

5. 实验与数据 #

Trajectory format ablation (Table 1, 20 questions). Structuring the trajectory is the load-bearing move — raw/naive logs give zero benefit over no trajectory (25% acc, 22.62 steps), while markdown/JSON roughly double accuracy (50%) and cut ~6 steps. Markdown is chosen for consistency with external-knowledge files and readability.

Format	Avg Steps	Accuracy (%)
No trajectory	22.62	25
Naive (raw)	22.62	25
Markdown	16.50	50
JSON	15.12	50



Paper's Table 2 (main results). AgentSM (claude-4) hits 52.2 BigQuery / 35.0 Snowflake / 51.9 SQLite / 44.8 overall at 16.4 avg steps and 247s latency, vs SpiderAgent 28.7 overall and CodingAgent 24.7. Note the honest cost: AgentSM uses more input tokens (~300K vs 200K) — the win is in steps and latency, not raw token count. The gold-tables row (57.6%) is the schema-linking oracle ceiling.



Paper's Figure 5 ("median steps split into exploration vs. execution and validation"). This is the mechanism made visible: the baseline spends 65% of median-20 steps exploring; AgentSM spends only 44% of median-16, transitioning to execution far earlier. The reader should notice the +4-step gap is entirely absorbed exploration.



Paper's Figure 4. (a) Over 327 steps the mean tool calls/step is 0.87 (mostly 0 or 1) — agents almost never chain tools within a step, wasting whole steps on deterministic routines. (b) Shows the concrete merge get_ext + get_ddl ⇒ local_exploration. Together they justify composition: if the agent won't chain tools itself, pre-chain them.



Paper's Figure 2. Read DDL CSV = 100%, Format CSV = 66.7%, External Knowledge = 66.7%, Vector Search = 33.3%. The near-universal early actions are exactly what synthetic-trajectory reuse eliminates; the low vector-search rate motivates not hard-coding it (Opportunity 2).

Component ablation (Table 3, 75 questions). Removing either component is catastrophic: no-trajectory-reading drops EX to 17.3 (−34.7) and adds +4.37 steps; no-composite-tools drops to 16.2 (−35.8), adds +4.77 steps and +46K input tokens / +271s latency. The full method: 52.0 EX, 15.99 steps.

Method	EX (%)	ΔEX	Avg Steps	ΔSteps	ΔInput	ΔTime (s)
No trajectory reading	17.3	−34.7	20.36	+4.37	+8K	+96.2
No composite tools	16.2	−35.8	20.76	+4.77	+46K	+271.3
Full method	52.0	—	15.99	—	—	—

Error analysis. Of 245 errors: 44% Snowflake (worst), 34% BigQuery, 22% SQLite. Within Snowflake, 30% are schema-linking (nested schemas), 5% step-budget, rest logic/dialect. Memory helps schema-linking most; it does not help complex-math / intricate-CTE reasoning. Per-domain accuracy ranges 60–78% (city/weather/census) down to 14–40% (github_repos/idc).

6. 论证链 #

#	Step	Support (paper-internal)
1	Agent exploration on a given DB is highly repetitive and thus reusable.	Fig 2 (100% read-DDL etc.); ref [17] <10–20% distinct trajectories.
2	Reuse is only effective if traces are structured, not raw.	Table 1: raw = no gain (25%); markdown/JSON = 50%.
3	Reusable traces can be manufactured offline via synthetic questions, decoupling memory from real query volume.	Algorithm 1 + §3.2 synthetic-question exploration.
4	Retrieve by same-DB filter then max-similarity to inject the right prior trace.	Eqs. 1–3; §3.2 "similar questions probe the same tables."
5	Agents under-chain tools (mean 0.87/step), so pre-merging frequent subsequences saves steps.	Fig 4a distribution; Eq. composite rule with $\theta$.
6	Together these shift steps from exploration to execution, raising accuracy under step/latency budgets.	Fig 5 (65:35→44:56); Table 3 (both components each ~+35 pts EX).
7	Net effect: SOTA 44.8% without a reasoning model; residual errors are schema-linking-bound.	Table 2 (44.8 vs 57.6 gold); §4.4 Snowflake 30% schema-linking errors.

7. 实现 cross-reference #

Implementation is not in the paper body; core artifacts are deferred to the authors' repository via footnotes:

• Synthetic-question generation prompts (Algorithm 1): https://tinyurl.com/xcey6h33
• Step-classification regex + trajectory-structure generation: https://tinyurl.com/492ymhcn

Reproducible stack from §4.1: smolagents (agent framework, [8]), MiniLM-L6-v2 embeddings ([23]) + FAISS ([10]) for $\text{sim}$, vLLM on 8×A100-80GB for the served 7B, Claude Haiku 4.5 as the lightweight header-generator, backends BigQuery/Snowflake/SQLite. [实现未公开] for the composite-tool support threshold $\theta$, the exact synthetic-question budget $N$, and the regex ruleset (only described, values not in paper).

核心技术壁垒 (dedicated). The hardest-to-replicate insight is offline synthetic-question trajectory manufacturing: rather than passively logging real traffic (cold-start problem, coverage skew), AgentSM proactively generates questions per-DB (budget-allocated toward large/complex schemas, Algorithm 1 Lines 6–11), runs an exploration-only agent on them, and banks the resulting dense traces. Replicating the format (markdown + regex classification + FAISS retrieval) is easy; replicating the quality and coverage of the synthetic distribution — which is what actually eliminates redundant exploration on unseen real queries — depends on unpublished prompt engineering and budget-allocation tuning.

关键实现细节 (easy-to-miss).

1. Step classification is regex/tool-pattern based, not positional — because agents re-enter the exploration phase mid-trajectory on errors (e.g. WITH ... ⇒ execution, file-read ⇒ exploration). LLM classification was tried and dropped for cost/latency at "comparable accuracy."
2. Per-phase segmented storage means the agent selectively loads only the phase-relevant trajectory segment (e.g. exploration segment during exploration), which is what avoids the lost-in-the-middle penalty of dumping a whole raw trace.