# SQL Performance Critic Framework (ISO/IEC 9075:2023)

This framework guides the Critic role when evaluating SQL code, database schemas, and query designs for performance optimization, efficiency, and scalability. This critic focuses on query optimization, indexing strategies, execution plan analysis, and performance monitoring to ensure efficient database operations.

## SQL Performance Evaluation Areas

### 1. Query Optimization and Efficiency
**What to Look For:**
- Efficient query design and execution plans
- Proper use of joins, subqueries, and set operations
- Avoidance of unnecessary full table scans
- Optimization of WHERE clauses and filtering conditions
- Efficient use of aggregate functions and grouping

**Common Problems:**
- Inefficient queries with poor execution plans
- Unnecessary full table scans due to missing indexes
- Overuse of subqueries or correlated subqueries
- Poor WHERE clause optimization
- Inefficient use of aggregate functions

**Evaluation Questions:**
- Are queries optimized for performance and scalability?
- Are execution plans reviewed and optimized?
- Are unnecessary full table scans avoided?
- Are WHERE clauses optimized for efficient filtering?
- Are aggregate functions used efficiently?

### 2. Indexing Strategy and Management
**What to Look For:**
- Appropriate use of indexes to speed up data access
- Proper index selection for query patterns
- Avoidance of redundant or unused indexes
- Regular index maintenance and optimization
- Monitoring of index usage and performance impact

**Common Problems:**
- Missing indexes on frequently queried columns
- Redundant or duplicate indexes
- Unused indexes consuming storage and maintenance overhead
- Poor index selection for query patterns
- Lack of index maintenance and monitoring

**Evaluation Questions:**
- Are indexes used appropriately to speed up data access?
- Are indexes selected based on actual query patterns?
- Are redundant or unused indexes identified and removed?
- Is index maintenance performed regularly?
- Is index usage monitored and optimized?

### 3. Schema Design for Performance
**What to Look For:**
- Appropriate normalization level for performance requirements
- Efficient data types and storage optimization
- Proper handling of large objects and BLOB data
- Partitioning strategies for large tables
- Efficient storage and retrieval patterns

**Common Problems:**
- Over-normalization causing excessive joins
- Poor data type selection affecting storage and performance
- Inefficient handling of large objects
- Missing partitioning for large tables
- Poor storage patterns for frequently accessed data

**Evaluation Questions:**
- Is normalization balanced with performance needs?
- Are data types chosen for efficiency and storage optimization?
- Are large objects handled efficiently?
- Is partitioning implemented for large tables?
- Are storage patterns optimized for access patterns?

### 4. Execution Plan Analysis
**What to Look For:**
- Analysis of query execution plans for optimization opportunities
- Identification of performance bottlenecks
- Monitoring of query performance metrics
- Optimization of join strategies and access methods
- Regular performance testing and benchmarking

**Common Problems:**
- Poor execution plans leading to slow queries
- Missing performance monitoring and analysis
- Inefficient join strategies
- Lack of performance testing and benchmarking
- Ignoring execution plan warnings and recommendations

**Evaluation Questions:**
- Are execution plans analyzed for optimization opportunities?
- Are performance bottlenecks identified and addressed?
- Is query performance monitored and tracked?
- Are join strategies optimized for efficiency?
- Is performance testing and benchmarking performed?

### 5. Resource Management and Scalability
**What to Look For:**
- Efficient use of database resources (CPU, memory, I/O)
- Proper connection pooling and resource management
- Scalability considerations for growing data volumes
- Monitoring of resource utilization and bottlenecks
- Optimization of concurrent access patterns

**Common Problems:**
- Inefficient resource utilization
- Poor connection management leading to resource exhaustion
- Lack of scalability planning for data growth
- Missing monitoring of resource utilization
- Inefficient concurrent access patterns

**Evaluation Questions:**
- Are database resources used efficiently?
- Is connection pooling implemented properly?
- Is the system designed for scalability?
- Is resource utilization monitored and optimized?
- Are concurrent access patterns optimized?

## ISO/IEC Standards-Specific Criticism Process

### Step 1: Query Performance Analysis
1. **Analyze Query Efficiency**: Are queries optimized for performance?
2. **Review Execution Plans**: Are execution plans analyzed and optimized?
3. **Check Query Patterns**: Are common query patterns optimized?
4. **Assess Performance Metrics**: Are performance metrics monitored?

### Step 2: Indexing Assessment
1. **Check Index Usage**: Are indexes used appropriately for query patterns?
2. **Evaluate Index Selection**: Are indexes selected based on actual usage?
3. **Assess Index Maintenance**: Is index maintenance performed regularly?
4. **Review Index Performance**: Is index performance monitored and optimized?

### Step 3: Schema Performance Analysis
1. **Check Normalization**: Is normalization balanced with performance needs?
2. **Evaluate Data Types**: Are data types chosen for efficiency?
3. **Assess Storage Patterns**: Are storage patterns optimized for access?
4. **Review Partitioning**: Is partitioning implemented for large tables?

### Step 4: Execution Plan Analysis
1. **Check Execution Plans**: Are execution plans analyzed for optimization?
2. **Evaluate Join Strategies**: Are join strategies optimized?
3. **Assess Access Methods**: Are access methods efficient?
4. **Review Performance Testing**: Is performance testing performed?

### Step 5: Resource Management Assessment
1. **Check Resource Utilization**: Are database resources used efficiently?
2. **Evaluate Connection Management**: Is connection pooling implemented?
3. **Assess Scalability**: Is the system designed for scalability?
4. **Review Monitoring**: Is resource utilization monitored?

## ISO/IEC Standards-Specific Criticism Guidelines

### Focus on Performance Optimization
**Good Criticism:**
- "The query plan shows a full table scan due to a missing index on the WHERE clause column."
- "The use of a correlated subquery is inefficient; consider using a JOIN instead."
- "The schema is over-normalized, causing excessive joins and poor performance."
- "The execution plan indicates a poor join strategy that could be optimized."

**Poor Criticism:**
- "This query is slow."
- "This needs optimization."
- "This is inefficient."

### Emphasize Performance Best Practices
**Good Criticism:**
- "The missing index on column X causes full table scans for this query pattern."
- "The correlated subquery in the SELECT clause can be replaced with a more efficient JOIN."
- "The data type VARCHAR(255) is inefficient for this use case; consider a more appropriate type."
- "The execution plan shows a nested loop join that could be optimized with proper indexing."

**Poor Criticism:**
- "This is not optimized."
- "This could be faster."
- "This is inefficient."

## ISO/IEC Standards-Specific Problem Categories

### Query Performance Problems
- **Inefficient Queries**: Poorly optimized queries with slow execution
- **Missing Indexes**: Lack of indexes on frequently queried columns
- **Poor Execution Plans**: Suboptimal execution plans leading to slow performance
- **Inefficient Joins**: Poor join strategies causing performance issues

### Indexing Problems
- **Missing Indexes**: Lack of indexes on columns used in WHERE, JOIN, and ORDER BY clauses
- **Redundant Indexes**: Duplicate or overlapping indexes
- **Unused Indexes**: Indexes that are not used by any queries
- **Poor Index Selection**: Indexes that don't match query patterns

### Schema Performance Problems
- **Over-Normalization**: Excessive normalization causing performance issues
- **Poor Data Types**: Inefficient data type selection affecting storage and performance
- **Missing Partitioning**: Lack of partitioning for large tables
- **Inefficient Storage**: Poor storage patterns for frequently accessed data

### Execution Plan Problems
- **Poor Execution Plans**: Suboptimal execution plans leading to slow queries
- **Missing Performance Analysis**: Lack of execution plan analysis and optimization
- **Inefficient Access Methods**: Poor access methods for data retrieval
- **Missing Performance Testing**: Lack of performance testing and benchmarking

### Resource Management Problems
- **Inefficient Resource Usage**: Poor utilization of database resources
- **Connection Issues**: Inadequate connection management and pooling
- **Scalability Problems**: Lack of scalability planning for data growth
- **Missing Monitoring**: Lack of resource utilization monitoring

## ISO/IEC Standards-Specific Criticism Templates

### For Query Performance Issues
```
Query Performance Issue: [Specific performance problem]
Problem: [What makes this query inefficient]
Impact: [Potential for slow queries, resource exhaustion, or scalability issues]
Evidence: [Query plans, execution times, or performance metrics]
Priority: [High/Medium/Low]
```

### For Indexing Issues
```
Indexing Issue: [Specific indexing problem]
Problem: [What makes this indexing strategy inadequate]
Impact: [Potential for slow queries due to missing or inefficient indexes]
Evidence: [Query patterns, execution plans, or index usage statistics]
Priority: [High/Medium/Low]
```

### For Schema Performance Issues
```
Schema Performance Issue: [Specific schema performance problem]
Problem: [What makes this schema design inefficient]
Impact: [Potential for poor performance due to schema design issues]
Evidence: [Schema design, normalization level, or data type choices]
Priority: [High/Medium/Low]
```

### For Execution Plan Issues
```
Execution Plan Issue: [Specific execution plan problem]
Problem: [What makes this execution plan inefficient]
Impact: [Potential for slow queries due to poor execution plans]
Evidence: [Execution plan analysis, query performance metrics]
Priority: [High/Medium/Low]
```

### For Resource Management Issues
```
Resource Management Issue: [Specific resource management problem]
Problem: [What makes this resource usage inefficient]
Impact: [Potential for resource exhaustion or scalability issues]
Evidence: [Resource utilization metrics, connection patterns]
Priority: [High/Medium/Low]
```

## ISO/IEC Standards-Specific Criticism Best Practices

### Do's
- **Focus on Performance Metrics**: Use specific performance measurements and metrics
- **Analyze Execution Plans**: Review and optimize query execution plans
- **Consider Scalability**: Evaluate performance for growing data volumes
- **Monitor Resource Usage**: Track and optimize resource utilization
- **Document Performance Decisions**: Clearly document performance optimization decisions

### Don'ts
- **Ignore Performance Issues**: Don't overlook performance problems
- **Accept Poor Performance**: Don't tolerate inefficient queries or schemas
- **Skip Performance Testing**: Don't ignore performance testing and benchmarking
- **Overlook Resource Usage**: Don't accept inefficient resource utilization
- **Forget Monitoring**: Don't skip performance monitoring and analysis

## ISO/IEC Standards-Specific Criticism Checklist

### Query Performance Assessment
- [ ] Are queries optimized for performance and scalability?
- [ ] Are execution plans reviewed and optimized?
- [ ] Are unnecessary full table scans avoided?
- [ ] Are WHERE clauses optimized for efficient filtering?
- [ ] Are aggregate functions used efficiently?

### Indexing Assessment
- [ ] Are indexes used appropriately to speed up data access?
- [ ] Are indexes selected based on actual query patterns?
- [ ] Are redundant or unused indexes identified and removed?
- [ ] Is index maintenance performed regularly?
- [ ] Is index usage monitored and optimized?

### Schema Performance Assessment
- [ ] Is normalization balanced with performance needs?
- [ ] Are data types chosen for efficiency and storage optimization?
- [ ] Are large objects handled efficiently?
- [ ] Is partitioning implemented for large tables?
- [ ] Are storage patterns optimized for access patterns?

### Execution Plan Assessment
- [ ] Are execution plans analyzed for optimization opportunities?
- [ ] Are performance bottlenecks identified and addressed?
- [ ] Is query performance monitored and tracked?
- [ ] Are join strategies optimized for efficiency?
- [ ] Is performance testing and benchmarking performed?

### Resource Management Assessment
- [ ] Are database resources used efficiently?
- [ ] Is connection pooling implemented properly?
- [ ] Is the system designed for scalability?
- [ ] Is resource utilization monitored and optimized?
- [ ] Are concurrent access patterns optimized?

## ISO/IEC Standards-Specific Evaluation Questions

### For Any SQL Code
1. **Are queries optimized for performance and scalability?**
2. **Are indexes used appropriately to speed up data access?**
3. **Are execution plans reviewed and optimized?**
4. **Is normalization balanced with performance needs?**
5. **Are performance metrics monitored and addressed?**
6. **Are database resources used efficiently?**
7. **Is connection pooling implemented properly?**
8. **Are performance bottlenecks identified and addressed?**
9. **Is performance testing and benchmarking performed?**
10. **Are concurrent access patterns optimized?**

### For Database Schemas
1. **Is normalization applied appropriately for performance requirements?**
2. **Are indexes used to support query performance?**
3. **Are data types chosen for efficiency and storage optimization?**
4. **Is partitioning implemented for large tables?**
5. **Are storage patterns optimized for access patterns?**

### For Stored Procedures and Triggers
1. **Are queries within procedures optimized for performance?**
2. **Are execution plans analyzed for optimization opportunities?**
3. **Is resource usage monitored and optimized?**
4. **Are performance bottlenecks identified and addressed?**
5. **Is performance testing performed for procedures?**

### For Performance and Scalability
1. **Are queries optimized for performance and scalability?**
2. **Are indexes used appropriately to speed up data access?**
3. **Are execution plans reviewed and optimized?**
4. **Is normalization balanced with performance needs?**
5. **Are performance metrics monitored and addressed?**

## SQL Performance Principles Applied

### "Optimize for Efficiency and Speed"
- Design queries for optimal performance
- Use appropriate indexes for query patterns
- Analyze and optimize execution plans

### "Balance Normalization with Performance"
- Apply normalization appropriately for the use case
- Consider performance implications of schema design
- Optimize storage patterns for access patterns

### "Monitor and Optimize Resources"
- Use database resources efficiently
- Implement proper connection management
- Monitor and optimize resource utilization

### "Plan for Scalability"
- Design for growing data volumes
- Consider concurrent access patterns
- Implement performance testing and benchmarking

### "Analyze and Optimize Execution"
- Review execution plans for optimization opportunities
- Identify and address performance bottlenecks
- Monitor query performance metrics