Database Optimizer Skill

Overview

Comprehensive database optimization skill for detecting and fixing N+1 queries, optimizing ORM configurations, analyzing slow queries, and implementing efficient batch operations.

Capabilities

1. N+1 Query Detection

• Identify N+1 query patterns
• Analyze ORM-generated queries
• Suggest eager loading strategies
• Detect inefficient lazy loading

2. Query Optimization

• Slow query analysis
• Execution plan review
• Index recommendations
• Query rewriting

3. ORM Configuration

• JPA/Hibernate tuning (Java)
• SQLAlchemy optimization (Python)
• Django ORM best practices
• Connection pool configuration

4. Batch Operations

• JDBC batch processing
• Bulk inserts/updates
• Transaction optimization
• Connection pooling

N+1 Query Detection

What is N+1?

-- 1 query to get all users
SELECT * FROM users;  -- Returns 100 users

-- N queries to get each user's orders (N = 100)
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
...
SELECT * FROM orders WHERE user_id = 100;

-- Total: 1 + 100 = 101 queries (INEFFICIENT!)

Solution: Eager Loading

-- 1 query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Total: 1 query (EFFICIENT!)

JPA/Hibernate Optimization (Java)

Detect N+1 in JPA

// ❌ BAD: Lazy loading causes N+1
@Entity
public class User {
    @OneToMany(fetch = FetchType.LAZY)  // Default
    private List<Order> orders;
}

List<User> users = userRepository.findAll();  // 1 query
for (User user : users) {
    user.getOrders().size();  // N queries!
}

Fix with Eager Loading

// ✅ GOOD: Use JOIN FETCH
@Query("SELECT u FROM User u LEFT JOIN FETCH u.orders")
List<User> findAllWithOrders();

// Or use EntityGraph
@EntityGraph(attributePaths = {"orders"})
List<User> findAll();

Batch Configuration

# application.properties
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true

Lazy Loading Configuration

// Set LazyCollectionOption for specific cases
@OneToMany(fetch = FetchType.LAZY)
@LazyCollection(LazyCollectionOption.FALSE)  // Eager loading
private List<Order> criticalOrders;

@OneToMany(fetch = FetchType.LAZY)
@LazyCollection(LazyCollectionOption.EXTRA)  // Smart queries
private List<Order> manyOrders;

SQLAlchemy Optimization (Python)

Detect N+1 in SQLAlchemy

# ❌ BAD: Lazy loading
users = session.query(User).all()  # 1 query
for user in users:
    print(user.orders)  # N queries!

Fix with Eager Loading

# ✅ GOOD: Use joinedload
from sqlalchemy.orm import joinedload

users = session.query(User).options(
    joinedload(User.orders)
).all()  # 1 query with JOIN

Django ORM Optimization

Detect N+1 in Django

# ❌ BAD: Lazy loading
users = User.objects.all()  # 1 query
for user in users:
    print(user.orders.all())  # N queries!

Fix with select_related / prefetch_related

# ✅ GOOD: Use select_related (ForeignKey, OneToOne)
users = User.objects.select_related('profile').all()

# ✅ GOOD: Use prefetch_related (ManyToMany, reverse ForeignKey)
users = User.objects.prefetch_related('orders').all()

Integration Scripts

detect_n_plus_one.py

Automated N+1 detection:

#!/usr/bin/env python3
import re
from collections import defaultdict

def analyze_query_log(log_file):
    """Analyze SQL log for N+1 patterns"""
    queries = []
    with open(log_file) as f:
        for line in f:
            if 'SELECT' in line.upper():
                # Extract SQL query
                query = re.search(r'SELECT.*?FROM.*?(?:WHERE|;)', line, re.IGNORECASE)
                if query:
                    queries.append(query.group(0))

    # Group similar queries
    query_patterns = defaultdict(int)
    for query in queries:
        # Normalize (replace IDs with placeholder)
        normalized = re.sub(r'\d+', 'ID', query)
        query_patterns[normalized] += 1

    # Report N+1 patterns (same query repeated many times)
    print("=== Potential N+1 Query Patterns ===")
    for pattern, count in query_patterns.items():
        if count > 10:  # Threshold
            print(f"\n⚠️  FOUND N+1 PATTERN (executed {count} times):")
            print(f"   {pattern[:100]}...")
            print(f"   💡 Consider using JOIN FETCH or eager loading")

analyze_query_log('sql_queries.log')

batch_analyzer.py

JPA batch configuration validator:

#!/usr/bin/env python3
import re

def analyze_jpa_config(properties_file):
    """Check JPA batch configuration"""
    with open(properties_file) as f:
        content = f.read()

    checks = {
        'jdbc.batch_size': r'hibernate\.jdbc\.batch_size\s*=\s*(\d+)',
        'order_inserts': r'hibernate\.order_inserts\s*=\s*(true|false)',
        'order_updates': r'hibernate\.order_updates\s*=\s*(true|false)',
    }

    print("=== JPA Batch Configuration Analysis ===\n")

    for name, pattern in checks.items():
        match = re.search(pattern, content)
        if match:
            value = match.group(1)
            print(f"✓ {name}: {value}")

            # Recommendations
            if name == 'jdbc.batch_size':
                batch_size = int(value)
                if batch_size < 20:
                    print(f"  ⚠️  Recommended: 20-50 (current: {batch_size})")
                elif batch_size > 100:
                    print(f"  ⚠️  Too high, may cause memory issues (current: {batch_size})")
        else:
            print(f"✗ {name}: NOT CONFIGURED")
            print(f"  💡 Add: hibernate.{name}=true")

    print("\n=== Recommendations ===")
    print("• Set batch_size between 20-50 for optimal performance")
    print("• Enable order_inserts and order_updates")
    print("• Monitor memory usage with batch operations")

lazy_loading_validator.py

Validate lazy loading configurations:

#!/usr/bin/env python3
import re
import os

def scan_lazy_loading(directory):
    """Scan Java entities for lazy loading issues"""
    issues = []

    for root, dirs, files in os.walk(directory):
        for file in files:
            if file.endswith('.java'):
                filepath = os.path.join(root, file)
                with open(filepath) as f:
                    content = f.read()

                # Check for lazy loading without proper handling
                lazy_patterns = re.findall(
                    r'@OneToMany.*?fetch\s*=\s*FetchType\.LAZY.*?private.*?(\w+);',
                    content,
                    re.DOTALL
                )

                if lazy_patterns:
                    # Check if there's JOIN FETCH in repository
                    has_join_fetch = 'JOIN FETCH' in content
                    has_entity_graph = '@EntityGraph' in content

                    if not (has_join_fetch or has_entity_graph):
                        issues.append({
                            'file': filepath,
                            'fields': lazy_patterns,
                            'suggestion': 'Add @EntityGraph or JOIN FETCH query'
                        })

    print("=== Lazy Loading Analysis ===\n")
    if issues:
        for issue in issues:
            print(f"⚠️  {issue['file']}")
            print(f"   Lazy fields: {', '.join(issue['fields'])}")
            print(f"   💡 {issue['suggestion']}\n")
    else:
        print("✓ No obvious lazy loading issues found")

scan_lazy_loading('./src/main/java')

Query Analysis

Slow Query Detection

-- MySQL: Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Queries > 2 seconds

-- PostgreSQL: Enable logging
ALTER DATABASE mydb SET log_min_duration_statement = 2000;

EXPLAIN Analysis

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- Look for:
-- • type = ALL (full table scan) ❌
-- • type = index (index scan) ✓
-- • type = ref (indexed lookup) ✓
-- • rows (estimated rows) - lower is better

Index Optimization

Missing Index Detection

-- Check for queries without indexes
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb';

-- Add indexes for frequently queried columns
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);

-- Composite indexes for multi-column queries
CREATE INDEX idx_user_status ON orders(user_id, status);

Index Guidelines

• Index foreign keys
• Index columns in WHERE clauses
• Index columns in ORDER BY
• Index columns in JOIN conditions
• Avoid over-indexing (slows writes)
• Use composite indexes for multi-column queries

Connection Pool Configuration

HikariCP (Java - Spring Boot)

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=600000
spring.datasource.hikari.connection-timeout=30000

Recommendations

• Pool size: (CPU cores × 2) + effective_spindle_count
• Timeout: 30 seconds connection timeout
• Max lifetime: 30 minutes (less than DB timeout)
• Monitor: Active connections, wait time, timeouts

Best Practices

1. Always use eager loading for collections accessed in loops
2. Enable batch operations for bulk inserts/updates
3. Configure connection pools appropriately
4. Monitor query logs for N+1 patterns
5. Add indexes for foreign keys and WHERE columns
6. Use EXPLAIN to analyze query plans
7. Set LazyCollectionOption.FALSE for critical relations
8. Avoid SELECT * - specify needed columns
9. Use pagination for large result sets
10. Cache frequently accessed data

Performance Metrics

• Query execution time: < 100ms for simple queries
• N+1 patterns: 0 (should never occur)
• Connection pool usage: < 80% max capacity
• Database CPU: < 70% under normal load
• Index hit ratio: > 95%
• Cache hit ratio: > 80% for read-heavy workloads

Requirements

# MySQL
sudo apt-get install mysql-client

# PostgreSQL
sudo apt-get install postgresql-client

# Python tools
pip install sqlalchemy psycopg2-binary pymysql

# Java tools
# HikariCP (included in Spring Boot)
# p6spy (SQL logging)