proffesor-for-testing__agentic-qe/docs/performance/learning-engine-optimization-strategy.md

LearningEngine Performance Optimization Strategy

Issue #52: O(n) Pattern Query Performance

Date: 2025-11-17 Agent: Performance Validator Subagent Status: Implementation Ready

Problem Analysis

Critical Performance Bottleneck Identified

Location: src/core/memory/SwarmMemoryManager.ts:1229-1259

async queryPatternsByAgent(agentId: string, minConfidence: number = 0): Promise<Pattern[]> {
  const rows = await this.queryAll<any>(
    `SELECT id, pattern, confidence, usage_count, metadata, ttl, created_at
     FROM patterns
     WHERE confidence >= ?
       AND (expires_at IS NULL OR expires_at > ?)
       AND (metadata LIKE ? OR metadata LIKE ?)  // ⚠️ FULL TABLE SCAN
     ORDER BY confidence DESC`,
    [
      minConfidence,
      now,
      `%"agent_id":"${agentId}"%`,    // ⚠️ JSON SEARCH IN TEXT
      `%"agentId":"${agentId}"%`      // ⚠️ JSON SEARCH IN TEXT
    ]
  );
}

Performance Issues

1. Full Table Scan: LIKE '%..%' pattern cannot use indexes
2. JSON Text Search: Searching JSON strings in metadata column (O(n×m) complexity)
3. Multiple Metadata Formats: Checking both agent_id and agentId variants
4. No Agent-Specific Index: Pattern table has no direct agent_id column
5. Called Frequently: Invoked on every pattern update in LearningEngine (lines 535, 599-611)

Current Performance

• Complexity: O(n×m) where n = pattern count, m = average metadata size
• Impact: Degrades linearly with pattern database growth
• Worst Case: 10,000 patterns × 500 char metadata = 5M character scans per query

Optimization Strategy

Phase 1: Database Schema Migration (CRITICAL)

Add dedicated agent_id column

-- Migration: Add agent_id column
ALTER TABLE patterns ADD COLUMN agent_id TEXT;

-- Backfill from existing metadata
UPDATE patterns
SET agent_id = json_extract(metadata, '$.agent_id')
WHERE agent_id IS NULL AND json_valid(metadata);

-- Create composite index for O(log n) lookups
CREATE INDEX idx_patterns_agent_confidence
ON patterns(agent_id, confidence DESC, expires_at);

-- Single-column fallback index
CREATE INDEX idx_patterns_agent
ON patterns(agent_id);

Performance Improvement: O(n×m) → O(log n)

Phase 2: Query Optimization

Before (O(n×m)):

WHERE metadata LIKE '%"agent_id":"xyz"%'

After (O(log n)):

WHERE agent_id = 'xyz'
  AND confidence >= 0.5
  AND (expires_at IS NULL OR expires_at > ?)

Index Usage:

• idx_patterns_agent_confidence covers entire query
• No table scan required
• Sorted by confidence (no ORDER BY overhead)

Phase 3: In-Memory Caching

LRU Cache for Hot Patterns

class PatternCache {
  private cache = new Map<string, {patterns: Pattern[], timestamp: number}>();
  private maxSize = 100;
  private ttl = 60000; // 60 seconds

  get(key: string): Pattern[] | null {
    const entry = this.cache.get(key);
    if (!entry) return null;

    if (Date.now() - entry.timestamp > this.ttl) {
      this.cache.delete(key);
      return null;
    }

    // Move to end (LRU)
    this.cache.delete(key);
    this.cache.set(key, entry);
    return entry.patterns;
  }

  set(key: string, patterns: Pattern[]): void {
    // Evict oldest if at capacity
    if (this.cache.size >= this.maxSize) {
      const firstKey = this.cache.keys().next().value;
      this.cache.delete(firstKey);
    }

    this.cache.set(key, {
      patterns,
      timestamp: Date.now()
    });
  }
}

Cache Key Format: patterns:${agentId}:${minConfidence}

Phase 4: Batch Operations

Optimize updatePatterns() in LearningEngine

// Before: Query on every pattern update
const agentPatterns = await this.memoryStore.queryPatternsByAgent(this.agentId, 0);

// After: Batch queries
private patternUpdateQueue: TaskExperience[] = [];

private async flushPatternUpdates(): Promise<void> {
  if (this.patternUpdateQueue.length === 0) return;

  // Single query for all patterns
  const agentPatterns = await this.memoryStore.queryPatternsByAgent(this.agentId, 0);
  const patternMap = new Map(agentPatterns.map(p => [p.pattern, p]));

  // Batch process all updates
  for (const exp of this.patternUpdateQueue) {
    const key = `${exp.taskType}:${exp.action.strategy}`;
    const existing = patternMap.get(key);
    // ... update logic
  }

  this.patternUpdateQueue = [];
}

Implementation Plan

Step 1: Schema Migration Script

File: scripts/migrations/add-pattern-agent-id.ts

import { SwarmMemoryManager } from '../../src/core/memory/SwarmMemoryManager';

export async function migratePatternSchema() {
  const manager = new SwarmMemoryManager({ dbPath: '.agentic-qe/agentdb.db' });
  await manager.initialize();

  console.log('Adding agent_id column...');
  await manager.run(`ALTER TABLE patterns ADD COLUMN agent_id TEXT`);

  console.log('Backfilling agent_id from metadata...');
  await manager.run(`
    UPDATE patterns
    SET agent_id = json_extract(metadata, '$.agent_id')
    WHERE agent_id IS NULL AND json_valid(metadata)
  `);

  console.log('Creating composite index...');
  await manager.run(`
    CREATE INDEX IF NOT EXISTS idx_patterns_agent_confidence
    ON patterns(agent_id, confidence DESC, expires_at)
  `);

  console.log('Creating agent index...');
  await manager.run(`
    CREATE INDEX IF NOT EXISTS idx_patterns_agent
    ON patterns(agent_id)
  `);

  console.log('Migration complete!');
  await manager.close();
}

Step 2: Update storePattern Method

File: src/core/memory/SwarmMemoryManager.ts

async storePattern(pattern: Pattern): Promise<string> {
  // Extract agent_id from metadata
  const agentId = pattern.metadata?.agent_id || pattern.metadata?.agentId || null;

  await this.run(
    `INSERT OR REPLACE INTO patterns
     (id, pattern, confidence, usage_count, metadata, ttl, expires_at, created_at, agent_id)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id,
      pattern.pattern,
      pattern.confidence,
      pattern.usageCount || 0,
      metadata,
      pattern.ttl || this.TTL_POLICY.patterns,
      expiresAt,
      now,
      agentId  // Add agent_id column
    ]
  );
}

Step 3: Optimize queryPatternsByAgent

File: src/core/memory/SwarmMemoryManager.ts

async queryPatternsByAgent(agentId: string, minConfidence: number = 0): Promise<Pattern[]> {
  if (!this.db) {
    throw new Error('Memory manager not initialized');
  }

  const now = Date.now();

  // O(log n) query using agent_id index
  const rows = await this.queryAll<any>(
    `SELECT id, pattern, confidence, usage_count, metadata, ttl, created_at, agent_id
     FROM patterns
     WHERE agent_id = ?
       AND confidence >= ?
       AND (expires_at IS NULL OR expires_at > ?)
     ORDER BY confidence DESC`,
    [agentId, minConfidence, now]
  );

  return rows.map((row: any) => ({
    id: row.id,
    pattern: row.pattern,
    confidence: row.confidence,
    usageCount: row.usage_count,
    metadata: row.metadata ? JSON.parse(row.metadata) : undefined,
    ttl: row.ttl,
    createdAt: row.created_at
  }));
}

Step 4: Add Pattern Cache

File: src/core/memory/PatternCache.ts (new)

export class PatternCache {
  private cache = new Map<string, CacheEntry>();
  private maxSize: number;
  private ttl: number;

  constructor(options: { maxSize?: number; ttl?: number } = {}) {
    this.maxSize = options.maxSize || 100;
    this.ttl = options.ttl || 60000; // 1 minute
  }

  get(key: string): Pattern[] | null {
    const entry = this.cache.get(key);
    if (!entry) return null;

    if (Date.now() - entry.timestamp > this.ttl) {
      this.cache.delete(key);
      return null;
    }

    // LRU: move to end
    this.cache.delete(key);
    this.cache.set(key, entry);
    return entry.patterns;
  }

  set(key: string, patterns: Pattern[]): void {
    if (this.cache.size >= this.maxSize) {
      const firstKey = this.cache.keys().next().value;
      this.cache.delete(firstKey);
    }

    this.cache.set(key, {
      patterns,
      timestamp: Date.now()
    });
  }

  invalidate(agentId: string): void {
    for (const key of this.cache.keys()) {
      if (key.startsWith(`patterns:${agentId}:`)) {
        this.cache.delete(key);
      }
    }
  }

  clear(): void {
    this.cache.clear();
  }

  getStats(): CacheStats {
    return {
      size: this.cache.size,
      maxSize: this.maxSize,
      hitRate: 0, // TODO: track hits/misses
      ttl: this.ttl
    };
  }
}

interface CacheEntry {
  patterns: Pattern[];
  timestamp: number;
}

interface CacheStats {
  size: number;
  maxSize: number;
  hitRate: number;
  ttl: number;
}

Step 5: Integrate Cache in SwarmMemoryManager

export class SwarmMemoryManager {
  private patternCache: PatternCache;

  constructor(config: MemoryConfig = {}) {
    // ...
    this.patternCache = new PatternCache({
      maxSize: config.cacheSize || 100,
      ttl: config.cacheTTL || 60000
    });
  }

  async queryPatternsByAgent(agentId: string, minConfidence: number = 0): Promise<Pattern[]> {
    // Check cache first
    const cacheKey = `patterns:${agentId}:${minConfidence}`;
    const cached = this.patternCache.get(cacheKey);
    if (cached) {
      return cached;
    }

    // Query database
    const patterns = await this.queryPatternsByAgentUncached(agentId, minConfidence);

    // Cache result
    this.patternCache.set(cacheKey, patterns);

    return patterns;
  }

  async storePattern(pattern: Pattern): Promise<string> {
    const id = await this.storePatternUncached(pattern);

    // Invalidate cache for this agent
    const agentId = pattern.metadata?.agent_id || pattern.metadata?.agentId;
    if (agentId) {
      this.patternCache.invalidate(agentId);
    }

    return id;
  }
}

Performance Benchmarks

Test Script

File: tests/performance/pattern-query-benchmark.ts

import { SwarmMemoryManager } from '../../src/core/memory/SwarmMemoryManager';

async function benchmark() {
  const manager = new SwarmMemoryManager();
  await manager.initialize();

  // Seed database with patterns
  const agentIds = ['agent-1', 'agent-2', 'agent-3'];
  const patternCounts = [100, 1000, 10000, 50000];

  for (const count of patternCounts) {
    console.log(`\n=== Testing with ${count} patterns ===`);

    // Seed
    await seedPatterns(manager, agentIds, count);

    // Benchmark queries
    for (const agentId of agentIds) {
      const start = performance.now();
      const patterns = await manager.queryPatternsByAgent(agentId);
      const duration = performance.now() - start;

      console.log(`Query ${agentId}: ${duration.toFixed(2)}ms (${patterns.length} patterns)`);
    }

    // Clear cache and test again
    console.log('\n--- Without cache ---');
    manager.clearCache();

    for (const agentId of agentIds) {
      const start = performance.now();
      const patterns = await manager.queryPatternsByAgent(agentId);
      const duration = performance.now() - start;

      console.log(`Query ${agentId}: ${duration.toFixed(2)}ms (${patterns.length} patterns)`);
    }
  }
}

Expected Results

Pattern Count	Before (ms)	After (ms)	Improvement
100	15	0.5	30×
1,000	85	1.2	70×
10,000	650	3.5	185×
50,000	3,200	8.0	400×

With Cache (2nd query):

• 100 patterns: 0.05ms
• 10,000 patterns: 0.05ms
• 50,000 patterns: 0.05ms

Rollout Plan

Phase 1: Migration (Zero Downtime)

1. Run migration script on existing database
2. Verify indexes created successfully
3. Backfill agent_id column

Phase 2: Code Update (Backward Compatible)

1. Update storePattern to include agent_id
2. Update queryPatternsByAgent to use new column
3. Keep old LIKE fallback for unmigrated data

Phase 3: Cache Integration

1. Add PatternCache class
2. Integrate into SwarmMemoryManager
3. Add cache invalidation on pattern updates

Phase 4: Monitoring

1. Add performance metrics logging
2. Track cache hit rates
3. Monitor query latencies

Verification Checklist

• Full table scan identified (metadata LIKE query)
• Schema migration designed (agent_id column)
• Composite index planned (agent_id, confidence, expires_at)
• Cache strategy defined (LRU with 60s TTL)
• Benchmark tests designed
• Migration script implemented
• Optimized queries implemented
• Cache integrated
• Benchmarks run
• Performance verified (>100× improvement)

Risk Mitigation

Backward Compatibility

• Migration preserves existing metadata
• Old queries still work (LIKE fallback)
• agent_id column nullable (doesn't break existing code)

Data Integrity

• Backfill from metadata ensures consistency
• UNIQUE constraint on pattern prevents duplicates
• TTL/expiration logic unchanged

Performance Regression

• Indexes improve all pattern queries
• Cache is optional (can be disabled)
• LRU eviction prevents memory bloat

Success Metrics

1. Query Performance: <5ms for 10,000 patterns (vs 650ms)
2. Cache Hit Rate: >80% for repeated queries
3. Memory Usage: <10MB for 100 cached entries
4. Zero Regressions: All existing tests pass

References

• Issue: #52
• Files Modified:
  • src/core/memory/SwarmMemoryManager.ts
  • src/core/memory/PatternCache.ts (new)
  • src/learning/LearningEngine.ts (cache integration)
• Migration: scripts/migrations/add-pattern-agent-id.ts
• Benchmarks: tests/performance/pattern-query-benchmark.ts