perf(api): Fix N+1 query problem in session list endpoint #202

New issue

Closed

opened 2026-01-24 17:14:42 +00:00 by jack · 0 comments

jack commented

2026-01-24 17:14:42 +00:00

Owner

Problem

DataRouter.listSessions() führt N+1 Queries aus:

// 1 Query für Sessions
const sessions = await sessionService.getSessions(...);

// N Queries für Observation Counts (eine pro Session!)
for (const session of sessions) {
  const count = await observations.countBySession(session.id);
}

// N weitere Queries für erste Prompts
for (const session of sessions) {
  const prompt = await prompts.getFirst(session.id);
}

Für 50 Sessions = 100+ Datenbank-Queries!

Hinweis: Dieses Issue verwendet die neuen Tabellennamen aus #197 (Database Schema Redesign).

Abhängigkeit

Sollte nach #197 umgesetzt werden, da dort die Tabellen umbenannt werden:

sdk_sessions → sessions
user_prompts → prompts

Lösung

1. Batch Query Methods

// ObservationRepository
async getCountsBySessionIds(sessionIds: string[]): Promise<Map<string, number>> {
  const results = await this.em.execute(`
    SELECT memory_session_id, COUNT(*) as count 
    FROM observations 
    WHERE memory_session_id IN (?)
    GROUP BY memory_session_id
  `, [sessionIds]);
  
  return new Map(results.map(r => [r.memory_session_id, r.count]));
}

// PromptRepository (vorher UserPromptRepository)
async getFirstPromptsBySessionIds(sessionIds: string[]): Promise<Map<string, Prompt>> {
  const results = await this.em.execute(`
    SELECT * FROM prompts 
    WHERE content_session_id IN (?)
    AND prompt_number = 1
  `, [sessionIds]);
  
  return new Map(results.map(r => [r.content_session_id, r]));
}

2. Refactored Route Handler

async listSessions(req, res) {
  const sessions = await sessionService.getSessions(...);
  const sessionIds = sessions.map(s => s.memory_session_id);
  
  // 2 Queries statt 100+
  const [obsCounts, firstPrompts] = await Promise.all([
    observations.getCountsBySessionIds(sessionIds),
    prompts.getFirstPromptsBySessionIds(sessionIds)
  ]);
  
  const enriched = sessions.map(s => ({
    ...s,
    observationCount: obsCounts.get(s.memory_session_id) || 0,
    firstPrompt: firstPrompts.get(s.content_session_id)
  }));
  
  return res.json(enriched);
}

Auswirkung

Metrik	Vorher	Nachher
Queries	100+	3
Latenz (50 sessions)	~500ms	~50ms
DB Load	Hoch	Niedrig

Betroffene Dateien

packages/backend/src/routes/data.ts
packages/database/src/repositories/ObservationRepository.ts
packages/database/src/repositories/PromptRepository.ts (vorher UserPromptRepository)

Akzeptanzkriterien

Neue Batch-Query Methoden in Repositories
Session-List Endpoint refactored
Latenz messbar reduziert
Alle Tests grün

## Problem `DataRouter.listSessions()` führt N+1 Queries aus: ```typescript // 1 Query für Sessions const sessions = await sessionService.getSessions(...); // N Queries für Observation Counts (eine pro Session!) for (const session of sessions) { const count = await observations.countBySession(session.id); } // N weitere Queries für erste Prompts for (const session of sessions) { const prompt = await prompts.getFirst(session.id); } ``` **Für 50 Sessions = 100+ Datenbank-Queries!** > **Hinweis:** Dieses Issue verwendet die neuen Tabellennamen aus #197 (Database Schema Redesign). ## Abhängigkeit Sollte nach #197 umgesetzt werden, da dort die Tabellen umbenannt werden: - `sdk_sessions` → `sessions` - `user_prompts` → `prompts` ## Lösung ### 1. Batch Query Methods ```typescript // ObservationRepository async getCountsBySessionIds(sessionIds: string[]): Promise<Map<string, number>> { const results = await this.em.execute(` SELECT memory_session_id, COUNT(*) as count FROM observations WHERE memory_session_id IN (?) GROUP BY memory_session_id `, [sessionIds]); return new Map(results.map(r => [r.memory_session_id, r.count])); } // PromptRepository (vorher UserPromptRepository) async getFirstPromptsBySessionIds(sessionIds: string[]): Promise<Map<string, Prompt>> { const results = await this.em.execute(` SELECT * FROM prompts WHERE content_session_id IN (?) AND prompt_number = 1 `, [sessionIds]); return new Map(results.map(r => [r.content_session_id, r])); } ``` ### 2. Refactored Route Handler ```typescript async listSessions(req, res) { const sessions = await sessionService.getSessions(...); const sessionIds = sessions.map(s => s.memory_session_id); // 2 Queries statt 100+ const [obsCounts, firstPrompts] = await Promise.all([ observations.getCountsBySessionIds(sessionIds), prompts.getFirstPromptsBySessionIds(sessionIds) ]); const enriched = sessions.map(s => ({ ...s, observationCount: obsCounts.get(s.memory_session_id) || 0, firstPrompt: firstPrompts.get(s.content_session_id) })); return res.json(enriched); } ``` ## Auswirkung | Metrik | Vorher | Nachher | |--------|--------|---------| | Queries | 100+ | 3 | | Latenz (50 sessions) | ~500ms | ~50ms | | DB Load | Hoch | Niedrig | ## Betroffene Dateien - `packages/backend/src/routes/data.ts` - `packages/database/src/repositories/ObservationRepository.ts` - `packages/database/src/repositories/PromptRepository.ts` (vorher UserPromptRepository) ## Akzeptanzkriterien - [ ] Neue Batch-Query Methoden in Repositories - [ ] Session-List Endpoint refactored - [ ] Latenz messbar reduziert - [ ] Alle Tests grün