performance-optimization

What it does and when to use it

Performance optimization patterns for the HMIS project. Use when optimizing slow queries, improving autocomplete performance, tuning database queries, reducing page load times, optimizing report generation, or investigating performance issues.

How it works

1. Core Principles
2. Autocomplete Optimization (85-90% improvement)
3. Query Optimization
4. MySQL Performance
5. Report Optimization

---
name: performance-optimization
description: >
  Performance optimization patterns for the HMIS project. Use when optimizing slow
  queries, improving autocomplete performance, tuning database queries, reducing page
  load times, optimizing report generation, or investigating performance issues.
user-invocable: true
---

# Performance Optimization Guide

## Core Principles

1. **Use DTOs throughout** - Avoid converting to entities until necessary
2. **Cache autocomplete results** - Preserve full DTO data for converters
3. **Skip unnecessary operations** - Use configuration and conditional logic
4. **Defer expensive operations** - Move to background or later in workflow

## Autocomplete Optimization (85-90% improvement)

Key pattern from Pharmacy Retail Sale (639ms -> 50ms):

1. **Enhance DTO** with fields needed for lightweight entity creation
2. **Cache autocomplete results** in controller for converter reuse
3. **Skip discount calculations** when no discount scheme active
4. **Defer item instructions** loading to later in workflow

## Query Optimization

- Use **DTO constructor queries** instead of entity queries for display
- Always include `retired = false` in WHERE clauses
- Use **indexed columns** in WHERE and ORDER BY
- Add **pagination** to limit result sets
- Use **JPQL aggregation** (SUM, COUNT, GROUP BY) instead of Java loops

## MySQL Performance

- Enable slow query log for queries > 1 second
- Use `EXPLAIN` to analyze query plans
- Add indexes for frequently queried columns
- Consider covering indexes for common queries

## Report Optimization

- Use DTO queries for report data
- Pre-aggregate data in queries instead of Java
- Implement lazy loading for large datasets
- Cache report results when data doesn't change frequently

For detailed guides, read:
- [Autocomplete Optimization](../../../developer_docs/performance/autocomplete-optimization-guide.md)
- [MySQL Performance](../../../developer_docs/database/mysql-performance-configuration.md)

Versions

Database snapshot: 13.11.2026 · package version 2d384dfe23b3 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

• 90sql-optimization-patterns
  Provides SQL optimization patterns for query performance, indexing strategies, schema design, and database tuning. Use when optimizing slow queries, designing indexes, or tuning database performance.
• 100optimizing-performance
  Analyzes and optimizes application performance across frontend, backend, and database layers. Use when diagnosing slowness, improving load times, optimizing queries, reducing bundle size, or when asked about performance issues.
• 98database-guide
  MySQL database development guide for the HMIS project. Use when working with database queries, SQL debugging, database schema investigation, MySQL performance tuning, credential management, or database connection troubleshooting.
• 98database-guide
  MySQL database development guide for the HMIS project. Use when working with database queries, SQL debugging, database schema investigation, MySQL performance tuning, credential management, or database connection troubleshooting.
• 100database-optimizer
  Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies,…
• 100laravel-database-optimization
  Laravel database optimization patterns. Use when writing Eloquent queries, creating migrations, configuring caching, debugging slow queries, or optimizing database performance. Triggers on tasks involving N+1 queries, indexing, Redis caching, pagination, or database transactions.