path: root/src/lib/db/perf.ts

/**
 * Database Performance Optimizations Documentation
 * ===============================================
 * 
 * 1. SRS Card Fetching Optimization
 * ---------------------------------
 * Problem: When processing card reviews in the SRS system, the application was fetching an entire
 * lesson's worth of cards just to retrieve a single updated card. This was inefficient, especially
 * for lessons with many cards.
 * 
 * Solution: Implemented a dedicated `fetchCardById` method in DatabaseHandler that retrieves only
 * the specific card needed with all its associated data (expression, progress, etc.). This method
 * is used in SRSStudyService.processReview to efficiently fetch just the updated card after a review.
 * 
 * Impact:
 * - Reduced database query load by eliminating unnecessary card fetches
 * - Fixed the "Failed to fetch updated card data" error that occurred when processing reviews
 * - Made card reviews more reliable and efficient
 * 
 * Implementation details:
 * 1. Added fetchCardById method to DatabaseHandler class
 * 2. Updated SRSStudyService.processReview to use fetchCardById instead of fetchLesson
 * 3. Maintained consistent timing measurements for performance monitoring
 * 
 * 2. SQLite Optimization Techniques
 * --------------------------------
 * - WAL (Write-Ahead Logging) mode enabled for better concurrency
 * - Increased cache size to 8MB for improved read performance
 * - Temp tables stored in memory rather than disk
 * - Reduced synchronous mode to NORMAL for better write performance
 * - Added strategic indexes on frequently queried columns
 * 
 * 3. JSON Processing Optimization
 * ------------------------------
 * - Measured and isolated JSON processing time from query execution time
 * - Confirmed that database queries (~329ms) were the primary bottleneck rather than 
 *   JSON processing (~0.8ms)
 * 
 * 4. Query-Level Optimizations
 * ---------------------------
 * - Used proper indexing for user_progress, expressions, and cards_lessons tables
 * - Optimized JOIN conditions to ensure efficient execution plans
 * - Used parameterized queries to take advantage of SQLite's query cache
 *
 * Database performance optimization suggestions
 */

// Optimized version of fetchLesson that splits the query into multiple smaller queries
// to reduce the complexity and improve performance
export function optimizedFetchLesson() {
  return `
  // Split into multiple queries for better performance
  
  // 1. First get the lesson information (small query)
  console.time("fetchLesson-lessonInfo");
  const lessonQuery = this.db.query(\`
    SELECT 
      l.name, 
      l.description, 
      ll.lang as llang,
      (SELECT COUNT(DISTINCT card_id) FROM cards_lessons WHERE lesson_id = ?) AS total_card_count
    FROM lessons l
    JOIN lang_lessons ll ON l.id = ll.lesson_id
    WHERE l.id = ?
    LIMIT 1
  \`);
  
  const lessonInfo = lessonQuery.get(lessonId, lessonId);
  console.timeEnd("fetchLesson-lessonInfo");
  
  if (!lessonInfo) {
    console.timeEnd("fetchLesson-total");
    return { error: "Lesson not found" };
  }
  
  // 2. Get the card IDs that need review (faster query)
  console.time("fetchLesson-cardIds");
  const cardIdsQuery = this.db.query(\`
    SELECT DISTINCT cards.id as cid
    FROM cards_lessons cl
    JOIN cards ON cards.id = cl.card_id
    LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?
    WHERE cl.lesson_id = ? AND (up.next_review_date IS NULL OR up.next_review_date < ?)
    \${random ? "ORDER BY RANDOM()" : "ORDER BY cards.id"}
    LIMIT ? OFFSET ?
  \`);
  
  const cardIdsResult = cardIdsQuery.all(userId, lessonId, tomorrow.getTime(), size, offset);
  console.timeEnd("fetchLesson-cardIds");
  
  if (cardIdsResult.length === 0) {
    console.timeEnd("fetchLesson-total");
    return { error: "No cards due for review" };
  }
  
  // Extract card IDs into a comma-separated list for the IN clause
  const cardIds = cardIdsResult.map((row: any) => row.cid).join(',');
  
  // 3. Get card data for those IDs
  console.time("fetchLesson-cardData");
  const cardDataQuery = this.db.query(\`
    SELECT 
      cards.text, cards.note, cards.id as cid,
      up.id as upid,
      up.repetition_count,
      up.ease_factor,
      up.interval,
      up.next_review_date,
      up.last_reviewed,
      up.is_mastered
    FROM cards
    LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?
    WHERE cards.id IN (\${cardIds})
  \`);
  
  const cardDataResult = cardDataQuery.all(userId);
  console.timeEnd("fetchLesson-cardData");
  
  // 4. For each card, get the expression data
  console.time("fetchLesson-expressionData");
  const cards = [];
  
  for (const cardData of cardDataResult) {
    const expressionQuery = this.db.query(\`
      SELECT 
        e.id as eid,
        e.spelling,
        e.lang,
        e.frequency,
        e.type,
        e.syllables,
        e.ipa,
        e.prosody,
        e.confidence,
        (CASE WHEN bm.word_id IS NULL THEN 0 ELSE 1 END) as is_bookmarked,
        (SELECT
          json_group_array(json_object(
            'pos', pos,
            'senses', s.senses,
            'forms', forms,
            'etymology', etymology,
            'related', related)
          )
          FROM senses s WHERE s.parent_id = e.id
        ) as senses_array
      FROM cards_expressions ce
      JOIN expressions e ON e.id = ce.expression_id
      LEFT JOIN bookmarks bm ON bm.word_id = e.id AND bm.user_id = ?
      WHERE ce.card_id = ?
      LIMIT 1
    \`);
    
    const expressionData = expressionQuery.get(userId, cardData.cid);
    
    if (expressionData) {
      // Process expression data
      let senses = [];
      try {
        const sense_array = JSON.parse(expressionData.senses_array || '[]');
        senses = sense_array.map((s: any) => {
          try {
            const sensesData = s.senses ? JSON.parse(s.senses) : [];
            const relatedData = s.related ? JSON.parse(s.related) : [];
            const formsData = s.forms ? JSON.parse(s.forms) : [];
            return { 
              ...s, 
              senses: sensesData, 
              related: relatedData, 
              forms: formsData 
            };
          } catch (e) {
            return { ...s, senses: [], related: [], forms: [] };
          }
        });
      } catch (e) {
        // Handle parse error
        console.error("Error parsing senses data:", e);
      }
      
      const expression = {
        isBookmarked: expressionData.is_bookmarked > 0,
        ipa: expressionData.ipa ? JSON.parse(expressionData.ipa) : {},
        prosody: expressionData.prosody ? JSON.parse(expressionData.prosody) : {},
        syllables: expressionData.syllables,
        frequency: expressionData.frequency,
        type: expressionData.type,
        lang: expressionData.lang,
        spelling: expressionData.spelling,
        id: expressionData.eid,
        confidence: expressionData.confidence,
        senses,
      };
      
      const progress = !cardData.upid
        ? DEFAULT_SRS
        : {
            repetitionCount: cardData.repetition_count,
            easeFactor: cardData.ease_factor,
            interval: cardData.interval,
            nextReviewDate: cardData.next_review_date,
            lastReviewed: cardData.last_reviewed,
            isMastered: cardData.is_mastered,
          };
      
      const card = {
        text: cardData.text,
        note: cardData.note,
        id: cardData.cid,
        expression,
        progress,
      };
      
      cards.push(card);
    }
  }
  console.timeEnd("fetchLesson-expressionData");
  
  const lesson = {
    id: lessonId,
    name: lessonInfo.name,
    description: lessonInfo.description,
    language: lessonInfo.llang,
    cardCount: lessonInfo.total_card_count,
  };
  
  console.timeEnd("fetchLesson-total");
  return { ok: { lesson, cards } };
  `;
}

// Additional performance optimization ideas
export const performanceOptimizations = [
  {
    area: "Indexing",
    description: "Add appropriate indexes to speed up common queries",
    implementation: "See indexes.sql for specific index definitions"
  },
  {
    area: "Query Splitting",
    description: "Split complex queries into multiple simpler queries",
    implementation: "Break down large JOIN operations into multiple targeted queries"
  },
  {
    area: "Caching",
    description: "Implement a caching layer for frequently accessed data",
    implementation: `
      // Simple in-memory cache implementation
      const cache = new Map();
      const CACHE_TTL = 1000 * 60 * 15; // 15 minutes
      
      function getCached(key: string) {
        const item = cache.get(key);
        if (!item) return null;
        
        if (Date.now() > item.expiry) {
          cache.delete(key);
          return null;
        }
        
        return item.value;
      }
      
      function setCache(key: string, value: any) {
        cache.set(key, {
          value,
          expiry: Date.now() + CACHE_TTL
        });
      }
    `
  },
  {
    area: "JSON Processing",
    description: "Optimize JSON parsing and serialization",
    implementation: `
      // Batch JSON parsing operations
      function safeJsonParse(jsonString: string, fallback: any = {}) {
        try {
          return JSON.parse(jsonString || '{}');
        } catch (e) {
          console.error("JSON parse error:", e);
          return fallback;
        }
      }
      
      // Process multiple JSON fields at once
      function processJsonFields(row: any, fields: string[]) {
        const result: Record<string, any> = {};
        
        for (const field of fields) {
          result[field] = safeJsonParse(row[field]);
        }
        
        return result;
      }
    `
  },
  {
    area: "SQLite Configuration",
    description: "Optimize SQLite connection parameters",
    implementation: `
      // Performance-focused SQLite configuration
      db.exec("PRAGMA journal_mode = WAL"); 
      db.exec("PRAGMA foreign_keys = ON");
      db.exec("PRAGMA cache_size = -8000"); // 8MB cache
      db.exec("PRAGMA temp_store = MEMORY"); 
      db.exec("PRAGMA synchronous = NORMAL"); 
      db.exec("PRAGMA mmap_size = 30000000000"); // 30GB memory map
    `
  },
  {
    area: "Query Optimization",
    description: "Avoid subqueries and complex joins where possible",
    implementation: "Use targeted queries with specific WHERE clauses rather than complex JOINs"
  }
];

// Caching layer for SRS data
export class SRSCache {
  private static instance: SRSCache;
  private cache: Map<string, { data: any, expiry: number }>;
  private TTL = 15 * 60 * 1000; // 15 minutes
  
  private constructor() {
    this.cache = new Map();
  }
  
  public static getInstance(): SRSCache {
    if (!SRSCache.instance) {
      SRSCache.instance = new SRSCache();
    }
    return SRSCache.instance;
  }
  
  public get<T>(key: string): T | null {
    const entry = this.cache.get(key);
    if (!entry) return null;
    
    if (Date.now() > entry.expiry) {
      this.cache.delete(key);
      return null;
    }
    
    return entry.data as T;
  }
  
  public set<T>(key: string, data: T, ttl?: number): void {
    this.cache.set(key, {
      data,
      expiry: Date.now() + (ttl || this.TTL)
    });
  }
  
  public clear(): void {
    this.cache.clear();
  }
  
  public delete(key: string): boolean {
    return this.cache.delete(key);
  }
}