Performance Optimization

Maximize MatsushibaDB performance with advanced optimization techniques, monitoring, and best practices.

Indexing Strategies

Creating Effective Indexes

-- Single column indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_posts_published ON posts(published);

-- Composite indexes for multi-column queries
CREATE INDEX idx_posts_author_date ON posts(author_id, created_at);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status, created_at);

-- Partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
CREATE INDEX idx_published_posts ON posts(created_at) WHERE published = 1;

-- Covering indexes to avoid table lookups
CREATE INDEX idx_posts_covering ON posts(author_id, published, created_at) 
INCLUDE (title, content);

-- Unique indexes for data integrity
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX idx_users_email ON users(email);

Index Analysis and Optimization

// Analyze query performance
function analyzeQuery(sql, params = []) {
    try {
        const explainQuery = `EXPLAIN QUERY PLAN ${sql}`;
        const plan = db.all(explainQuery, params);
        
        console.log('Query Plan:', plan);
        
        // Check for full table scans
        const hasTableScan = plan.some(step => 
            step.detail.includes('SCAN TABLE') && 
            !step.detail.includes('USING INDEX')
        );
        
        if (hasTableScan) {
            console.warn('Warning: Query may perform full table scan');
        }
        
        return {
            plan: plan,
            hasTableScan: hasTableScan,
            recommendations: generateIndexRecommendations(plan)
        };
    } catch (error) {
        throw error;
    }
}

// Generate index recommendations
function generateIndexRecommendations(plan) {
    const recommendations = [];
    
    plan.forEach(step => {
        if (step.detail.includes('SCAN TABLE') && !step.detail.includes('USING INDEX')) {
            const tableMatch = step.detail.match(/SCAN TABLE (\w+)/);
            if (tableMatch) {
                recommendations.push({
                    type: 'missing_index',
                    table: tableMatch[1],
                    suggestion: `Consider creating an index on ${tableMatch[1]} for better performance`
                });
            }
        }
    });
    
    return recommendations;
}

// Index usage statistics
function getIndexUsageStats() {
    try {
        const stats = db.all(`
            SELECT 
                name,
                tbl_name,
                sql,
                CASE 
                    WHEN name LIKE 'sqlite_autoindex_%' THEN 'auto'
                    ELSE 'manual'
                END as index_type
            FROM sqlite_master 
            WHERE type = 'index' 
            AND name NOT LIKE 'sqlite_%'
            ORDER BY tbl_name, name
        `);
        
        return stats;
    } catch (error) {
        throw error;
    }
}

Query Optimization

Prepared Statements

// Use prepared statements for repeated queries
class QueryOptimizer {
    constructor(db) {
        this.db = db;
        this.preparedStatements = new Map();
    }
    
    // Prepare statement with caching
    prepare(sql) {
        if (!this.preparedStatements.has(sql)) {
            const stmt = this.db.prepare(sql);
            this.preparedStatements.set(sql, stmt);
        }
        return this.preparedStatements.get(sql);
    }
    
    // Execute prepared statement
    execute(sql, params) {
        const stmt = this.prepare(sql);
        return stmt.run(params);
    }
    
    // Get single record
    get(sql, params) {
        const stmt = this.prepare(sql);
        return stmt.get(params);
    }
    
    // Get all records
    all(sql, params) {
        const stmt = this.prepare(sql);
        return stmt.all(params);
    }
    
    // Clean up prepared statements
    finalize() {
        for (const stmt of this.preparedStatements.values()) {
            stmt.finalize();
        }
        this.preparedStatements.clear();
    }
}

// Usage example
const optimizer = new QueryOptimizer(db);

// These queries will reuse the same prepared statement
const user1 = optimizer.get('SELECT * FROM users WHERE id = ?', [1]);
const user2 = optimizer.get('SELECT * FROM users WHERE id = ?', [2]);
const user3 = optimizer.get('SELECT * FROM users WHERE id = ?', [3]);

Query Optimization Techniques

// Pagination optimization
function getUsersPaginated(page, pageSize) {
    const offset = (page - 1) * pageSize;
    
    // Use LIMIT and OFFSET efficiently
    return db.all(`
        SELECT * FROM users 
        ORDER BY created_at DESC 
        LIMIT ? OFFSET ?
    `, [pageSize, offset]);
}

// Cursor-based pagination (more efficient for large datasets)
function getUsersCursor(cursor, pageSize) {
    const whereClause = cursor ? 'WHERE id < ?' : '';
    const params = cursor ? [cursor, pageSize] : [pageSize];
    
    return db.all(`
        SELECT * FROM users 
        ${whereClause}
        ORDER BY id DESC 
        LIMIT ?
    `, params);
}

// Batch operations
function batchInsertUsers(users) {
    try {
        db.run('BEGIN TRANSACTION');
        
        const stmt = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');
        
        for (const user of users) {
            stmt.run(user.name, user.email, user.age);
        }
        
        stmt.finalize();
        db.run('COMMIT');
        
        return { success: true, count: users.length };
    } catch (error) {
        db.run('ROLLBACK');
        throw error;
    }
}

// Query result caching
class QueryCache {
    constructor(ttl = 300000) { // 5 minutes default TTL
        this.cache = new Map();
        this.ttl = ttl;
    }
    
    get(key) {
        const item = this.cache.get(key);
        if (item && Date.now() - item.timestamp < this.ttl) {
            return item.data;
        }
        this.cache.delete(key);
        return null;
    }
    
    set(key, data) {
        this.cache.set(key, {
            data: data,
            timestamp: Date.now()
        });
    }
    
    clear() {
        this.cache.clear();
    }
}

// Usage with caching
const queryCache = new QueryCache();

function getCachedUsers() {
    const cacheKey = 'all_users';
    let users = queryCache.get(cacheKey);
    
    if (!users) {
        users = db.all('SELECT * FROM users ORDER BY created_at DESC');
        queryCache.set(cacheKey, users);
    }
    
    return users;
}

Connection Pooling

Advanced Connection Pool Management

const { MatsushibaPool } = require('matsushibadb');

class AdvancedConnectionPool {
    constructor(config) {
        this.pool = new MatsushibaPool({
            database: config.database,
            min: config.minConnections || 5,
            max: config.maxConnections || 20,
            acquireTimeoutMillis: config.acquireTimeout || 30000,
            createTimeoutMillis: config.createTimeout || 30000,
            destroyTimeoutMillis: config.destroyTimeout || 5000,
            idleTimeoutMillis: config.idleTimeout || 30000,
            reapIntervalMillis: config.reapInterval || 1000,
            createRetryIntervalMillis: config.createRetryInterval || 200,
            propagateCreateError: false
        });
        
        this.metrics = {
            totalConnections: 0,
            activeConnections: 0,
            idleConnections: 0,
            totalQueries: 0,
            averageQueryTime: 0
        };
    }
    
    async executeQuery(sql, params = []) {
        const startTime = Date.now();
        const connection = await this.pool.acquire();
        
        try {
            const result = await connection.all(sql, params);
            this.updateMetrics(Date.now() - startTime);
            return result;
        } finally {
            this.pool.release(connection);
        }
    }
    
    async executeTransaction(operations) {
        const connection = await this.pool.acquire();
        
        try {
            await connection.beginTransaction();
            
            const results = [];
            for (const operation of operations) {
                const result = await connection.run(operation.sql, operation.params);
                results.push(result);
            }
            
            await connection.commit();
            return { success: true, results: results };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            this.pool.release(connection);
        }
    }
    
    updateMetrics(queryTime) {
        this.metrics.totalQueries++;
        this.metrics.averageQueryTime = 
            (this.metrics.averageQueryTime * (this.metrics.totalQueries - 1) + queryTime) / 
            this.metrics.totalQueries;
    }
    
    getMetrics() {
        return {
            ...this.metrics,
            poolSize: this.pool.size,
            availableConnections: this.pool.available,
            waitingClients: this.pool.waiting
        };
    }
    
    async close() {
        await this.pool.destroy();
    }
}

// Usage
const pool = new AdvancedConnectionPool({
    database: 'app.db',
    minConnections: 5,
    maxConnections: 20
});

// Execute queries
const users = await pool.executeQuery('SELECT * FROM users WHERE active = ?', [1]);

// Execute transactions
const result = await pool.executeTransaction([
    { sql: 'INSERT INTO users (name, email) VALUES (?, ?)', params: ['John', '[email protected]'] },
    { sql: 'INSERT INTO profiles (user_id, bio) VALUES (?, ?)', params: [1, 'New user'] }
]);

Performance Monitoring

Database Performance Metrics

class PerformanceMonitor {
    constructor(db) {
        this.db = db;
        this.metrics = {
            queryCount: 0,
            totalQueryTime: 0,
            slowQueries: [],
            connectionCount: 0,
            cacheHits: 0,
            cacheMisses: 0
        };
    }
    
    // Monitor query performance
    async monitorQuery(sql, params, operation) {
        const startTime = Date.now();
        
        try {
            const result = await operation();
            const executionTime = Date.now() - startTime;
            
            this.metrics.queryCount++;
            this.metrics.totalQueryTime += executionTime;
            
            // Track slow queries
            if (executionTime > 1000) { // Queries taking more than 1 second
                this.metrics.slowQueries.push({
                    sql: sql,
                    params: params,
                    executionTime: executionTime,
                    timestamp: new Date()
                });
            }
            
            return result;
        } catch (error) {
            console.error('Query failed:', sql, error);
            throw error;
        }
    }
    
    // Get performance statistics
    getStats() {
        const averageQueryTime = this.metrics.queryCount > 0 
            ? this.metrics.totalQueryTime / this.metrics.queryCount 
            : 0;
        
        return {
            totalQueries: this.metrics.queryCount,
            averageQueryTime: averageQueryTime,
            slowQueriesCount: this.metrics.slowQueries.length,
            cacheHitRate: this.metrics.cacheHits / (this.metrics.cacheHits + this.metrics.cacheMisses) || 0,
            databaseSize: this.getDatabaseSize(),
            indexUsage: this.getIndexUsageStats()
        };
    }
    
    // Get database size
    getDatabaseSize() {
        try {
            const result = this.db.get(`
                SELECT page_count * page_size as size
                FROM pragma_page_count(), pragma_page_size()
            `);
            return result.size;
        } catch (error) {
            return 0;
        }
    }
    
    // Get index usage statistics
    getIndexUsageStats() {
        try {
            return this.db.all(`
                SELECT 
                    name,
                    tbl_name,
                    sql
                FROM sqlite_master 
                WHERE type = 'index' 
                AND name NOT LIKE 'sqlite_%'
            `);
        } catch (error) {
            return [];
        }
    }
    
    // Analyze slow queries
    analyzeSlowQueries() {
        const analysis = {
            mostCommonSlowQueries: {},
            averageSlowQueryTime: 0,
            recommendations: []
        };
        
        if (this.metrics.slowQueries.length === 0) {
            return analysis;
        }
        
        // Group slow queries by SQL pattern
        this.metrics.slowQueries.forEach(query => {
            const pattern = query.sql.replace(/\d+/g, '?'); // Normalize parameters
            if (!analysis.mostCommonSlowQueries[pattern]) {
                analysis.mostCommonSlowQueries[pattern] = {
                    count: 0,
                    totalTime: 0,
                    averageTime: 0
                };
            }
            
            analysis.mostCommonSlowQueries[pattern].count++;
            analysis.mostCommonSlowQueries[pattern].totalTime += query.executionTime;
        });
        
        // Calculate averages
        Object.keys(analysis.mostCommonSlowQueries).forEach(pattern => {
            const stats = analysis.mostCommonSlowQueries[pattern];
            stats.averageTime = stats.totalTime / stats.count;
        });
        
        // Generate recommendations
        Object.keys(analysis.mostCommonSlowQueries).forEach(pattern => {
            const stats = analysis.mostCommonSlowQueries[pattern];
            if (stats.count > 5 && stats.averageTime > 500) {
                analysis.recommendations.push({
                    query: pattern,
                    issue: 'Frequent slow query',
                    suggestion: 'Consider adding indexes or optimizing the query'
                });
            }
        });
        
        return analysis;
    }
}

// Usage
const monitor = new PerformanceMonitor(db);

// Wrap queries with monitoring
async function getUsers() {
    return await monitor.monitorQuery(
        'SELECT * FROM users WHERE active = ?',
        [1],
        () => db.all('SELECT * FROM users WHERE active = ?', [1])
    );
}

Memory Optimization

Memory Management Techniques

// Memory-efficient data processing
class MemoryOptimizer {
    constructor(db) {
        this.db = db;
        this.batchSize = 1000; // Process in batches
    }
    
    // Process large datasets in chunks
    async processLargeDataset(sql, params, processor) {
        const totalCount = this.db.get(`SELECT COUNT(*) as count FROM (${sql})`, params).count;
        let processed = 0;
        
        while (processed < totalCount) {
            const batch = this.db.all(`
                ${sql} 
                LIMIT ? OFFSET ?
            `, [...params, this.batchSize, processed]);
            
            await processor(batch);
            processed += batch.length;
            
            // Force garbage collection if available
            if (global.gc) {
                global.gc();
            }
        }
    }
    
    // Stream processing for very large datasets
    async streamProcess(sql, params, processor) {
        const stmt = this.db.prepare(sql);
        
        return new Promise((resolve, reject) => {
            stmt.each(
                params,
                (row) => {
                    try {
                        processor(row);
                    } catch (error) {
                        reject(error);
                    }
                },
                (err, count) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(count);
                    }
                    stmt.finalize();
                }
            );
        });
    }
    
    // Optimize database settings for memory
    optimizeMemorySettings() {
        // Set cache size based on available memory
        const cacheSize = Math.min(2000, Math.floor(process.memoryUsage().heapTotal / 1024 / 1024));
        
        this.db.run(`PRAGMA cache_size = ${cacheSize}`);
        this.db.run('PRAGMA temp_store = MEMORY');
        this.db.run('PRAGMA mmap_size = 268435456'); // 256MB
    }
}

// Usage
const optimizer = new MemoryOptimizer(db);
optimizer.optimizeMemorySettings();

// Process large user dataset
await optimizer.processLargeDataset(
    'SELECT * FROM users WHERE created_at > ?',
    ['2024-01-01'],
    async (batch) => {
        // Process batch
        console.log(`Processing ${batch.length} users`);
        // ... processing logic
    }
);

Performance Tuning

Database Configuration Optimization

-- Performance optimization settings
PRAGMA journal_mode = WAL;           -- Better concurrency
PRAGMA synchronous = NORMAL;          -- Balance between safety and speed
PRAGMA cache_size = 2000;            -- Increase cache size
PRAGMA temp_store = MEMORY;          -- Use memory for temp tables
PRAGMA mmap_size = 268435456;        -- 256MB memory mapping
PRAGMA optimize;                     -- Run query optimizer

-- Analyze tables for better query planning
ANALYZE;

-- Vacuum database to reclaim space
VACUUM;

-- Check database integrity
PRAGMA integrity_check;

Application-Level Optimizations

// Connection pooling configuration
const poolConfig = {
    min: 5,                    // Minimum connections
    max: 20,                   // Maximum connections
    acquireTimeoutMillis: 30000,
    createTimeoutMillis: 30000,
    destroyTimeoutMillis: 5000,
    idleTimeoutMillis: 30000,
    reapIntervalMillis: 1000,
    createRetryIntervalMillis: 200
};

// Query optimization middleware
function queryOptimizer(req, res, next) {
    const originalSend = res.send;
    
    res.send = function(data) {
        // Log slow responses
        const responseTime = Date.now() - req.startTime;
        if (responseTime > 1000) {
            console.warn(`Slow response: ${req.method} ${req.path} - ${responseTime}ms`);
        }
        
        originalSend.call(this, data);
    };
    
    next();
}

// Database health check
async function healthCheck() {
    try {
        const startTime = Date.now();
        await db.get('SELECT 1');
        const responseTime = Date.now() - startTime;
        
        return {
            status: 'healthy',
            responseTime: responseTime,
            timestamp: new Date().toISOString()
        };
    } catch (error) {
        return {
            status: 'unhealthy',
            error: error.message,
            timestamp: new Date().toISOString()
        };
    }
}

Best Practices

Create Appropriate Indexes

Create indexes on frequently queried columns and composite indexes for multi-column queries.

Use Prepared Statements

Always use prepared statements for repeated queries to improve performance and security.

Implement Connection Pooling

Use connection pooling for production applications to manage database connections efficiently.

Monitor Performance

Implement comprehensive performance monitoring to identify bottlenecks and optimize accordingly.

Optimize Queries

Use EXPLAIN QUERY PLAN to analyze and optimize query performance.

Use Pagination

Implement proper pagination for large result sets to avoid memory issues.

Cache Frequently Used Data

Implement caching strategies for frequently accessed data to reduce database load.

Regular Maintenance

Perform regular database maintenance including VACUUM and ANALYZE operations.

Performance optimization is an ongoing process. Regularly monitor your database performance, analyze slow queries, and implement optimizations based on your specific use case and data patterns.

🚀 Get Started

📦 Installation

🧠 Core Concepts

📖 Advanced Guides

🔧 API Reference

🛠️ Troubleshooting

Performance

Performance Optimization

Indexing Strategies

Creating Effective Indexes

Index Analysis and Optimization

Query Optimization

Prepared Statements

Query Optimization Techniques

Connection Pooling

Advanced Connection Pool Management

Performance Monitoring

Database Performance Metrics

Memory Optimization

Memory Management Techniques

Performance Tuning

Database Configuration Optimization

Application-Level Optimizations

Best Practices

🚀 Get Started

📦 Installation

🧠 Core Concepts

📖 Advanced Guides

🔧 API Reference

🛠️ Troubleshooting

​Performance Optimization

​Indexing Strategies

​Creating Effective Indexes

​Index Analysis and Optimization

​Query Optimization

​Prepared Statements

​Query Optimization Techniques

​Connection Pooling

​Advanced Connection Pool Management

​Performance Monitoring

​Database Performance Metrics

​Memory Optimization

​Memory Management Techniques

​Performance Tuning

​Database Configuration Optimization

​Application-Level Optimizations

​Best Practices

Performance Optimization

Indexing Strategies

Creating Effective Indexes

Index Analysis and Optimization

Query Optimization

Prepared Statements

Query Optimization Techniques

Connection Pooling

Advanced Connection Pool Management

Performance Monitoring

Database Performance Metrics

Memory Optimization

Memory Management Techniques

Performance Tuning

Database Configuration Optimization

Application-Level Optimizations

Best Practices