diff options
Diffstat (limited to 'src/lib/db/perf.ts')
| -rw-r--r-- | src/lib/db/perf.ts | 43 |
1 files changed, 43 insertions, 0 deletions
diff --git a/src/lib/db/perf.ts b/src/lib/db/perf.ts index a5b57c3..d805314 100644 --- a/src/lib/db/perf.ts +++ b/src/lib/db/perf.ts @@ -1,4 +1,47 @@ /** + * 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 */ |