me again but it works!

1 files changed, 317 insertions, 0 deletions

diff --git a/src/lib/db/perf.ts b/src/lib/db/perf.ts
new file mode 100644
index 0000000..a5b57c3
--- /dev/null
+++ b/src/lib/db/perf.ts
@@ -0,0 +1,317 @@
+/**
+ * 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);
+  }
+}
\ No newline at end of file