frappe-performance-optimizer_skill

Overview

This skill generates performance-optimized code, queries, caching patterns, and index recommendations tailored for Frappe applications. It focuses on eliminating slow queries, preventing N+1 patterns, and applying practical caching and bulk-operation strategies. The goal is measurable speed-ups with minimal disruption to existing code.

How this skill works

The skill inspects reported slow queries, schema usage, and common access patterns to propose optimized SQL, appropriate database indexes, and caching layers. It produces concrete code snippets for Frappe: efficient JOIN queries, cache wrappers using frappe.cache(), and bulk update patterns with single SQL operations. It can also recommend index definitions and where to place caches (in-memory vs. TTL).

When to use it

• When users report slow page loads or reports tied to database queries
• When audit or profiler shows N+1 queries or repeated lookups
• Before deploying reports or heavy-reporting views to production
• When implementing expensive calculations that can be reused
• When preparing bulk data migrations or updates

Best practices

• Prefer SELECT with JOINs and GROUP BY instead of per-row queries to avoid N+1 issues
• Add composite indexes that match WHERE and ORDER BY columns used by heavy queries
• Cache idempotent, read-heavy computations with explicit TTL and cache keys tied to relevant records
• Use bulk SQL updates/inserts for large batches and call frappe.db.commit() once after the batch
• Measure performance before and after changes using query profiling or explain plans

Example use cases

• Optimize a slow sales summary report by replacing per-invoice loops with a single aggregated JOIN and adding an index on (customer, posting_date, docstatus)
• Cache item price lookups per price list and customer using frappe.cache() with a 1-hour TTL
• Implement bulk activation/deactivation of Item records using a single UPDATE WHERE name IN (...) to reduce round-trips
• Add indexes and rewrite queries for stock ledger or other report-generating modules to drop execution time from minutes to seconds
• Detect and rewrite code paths that fetch child records in loops into batched queries