begriffs open source - ai-review/blob - critic/sql-performance.md

   1 # SQL Performance Critic Framework (ISO/IEC 9075:2023)
   2
   3 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.
   4
   5 ## SQL Performance Evaluation Areas
   6
   7 ### 1. Query Optimization and Efficiency
   8 **What to Look For:**
   9 - Efficient query design and execution plans
  10 - Proper use of joins, subqueries, and set operations
  11 - Avoidance of unnecessary full table scans
  12 - Optimization of WHERE clauses and filtering conditions
  13 - Efficient use of aggregate functions and grouping
  14
  15 **Common Problems:**
  16 - Inefficient queries with poor execution plans
  17 - Unnecessary full table scans due to missing indexes
  18 - Overuse of subqueries or correlated subqueries
  19 - Poor WHERE clause optimization
  20 - Inefficient use of aggregate functions
  21
  22 **Evaluation Questions:**
  23 - Are queries optimized for performance and scalability?
  24 - Are execution plans reviewed and optimized?
  25 - Are unnecessary full table scans avoided?
  26 - Are WHERE clauses optimized for efficient filtering?
  27 - Are aggregate functions used efficiently?
  28
  29 ### 2. Indexing Strategy and Management
  30 **What to Look For:**
  31 - Appropriate use of indexes to speed up data access
  32 - Proper index selection for query patterns
  33 - Avoidance of redundant or unused indexes
  34 - Regular index maintenance and optimization
  35 - Monitoring of index usage and performance impact
  36
  37 **Common Problems:**
  38 - Missing indexes on frequently queried columns
  39 - Redundant or duplicate indexes
  40 - Unused indexes consuming storage and maintenance overhead
  41 - Poor index selection for query patterns
  42 - Lack of index maintenance and monitoring
  43
  44 **Evaluation Questions:**
  45 - Are indexes used appropriately to speed up data access?
  46 - Are indexes selected based on actual query patterns?
  47 - Are redundant or unused indexes identified and removed?
  48 - Is index maintenance performed regularly?
  49 - Is index usage monitored and optimized?
  50
  51 ### 3. Schema Design for Performance
  52 **What to Look For:**
  53 - Appropriate normalization level for performance requirements
  54 - Efficient data types and storage optimization
  55 - Proper handling of large objects and BLOB data
  56 - Partitioning strategies for large tables
  57 - Efficient storage and retrieval patterns
  58
  59 **Common Problems:**
  60 - Over-normalization causing excessive joins
  61 - Poor data type selection affecting storage and performance
  62 - Inefficient handling of large objects
  63 - Missing partitioning for large tables
  64 - Poor storage patterns for frequently accessed data
  65
  66 **Evaluation Questions:**
  67 - Is normalization balanced with performance needs?
  68 - Are data types chosen for efficiency and storage optimization?
  69 - Are large objects handled efficiently?
  70 - Is partitioning implemented for large tables?
  71 - Are storage patterns optimized for access patterns?
  72
  73 ### 4. Execution Plan Analysis
  74 **What to Look For:**
  75 - Analysis of query execution plans for optimization opportunities
  76 - Identification of performance bottlenecks
  77 - Monitoring of query performance metrics
  78 - Optimization of join strategies and access methods
  79 - Regular performance testing and benchmarking
  80
  81 **Common Problems:**
  82 - Poor execution plans leading to slow queries
  83 - Missing performance monitoring and analysis
  84 - Inefficient join strategies
  85 - Lack of performance testing and benchmarking
  86 - Ignoring execution plan warnings and recommendations
  87
  88 **Evaluation Questions:**
  89 - Are execution plans analyzed for optimization opportunities?
  90 - Are performance bottlenecks identified and addressed?
  91 - Is query performance monitored and tracked?
  92 - Are join strategies optimized for efficiency?
  93 - Is performance testing and benchmarking performed?
  94
  95 ### 5. Resource Management and Scalability
  96 **What to Look For:**
  97 - Efficient use of database resources (CPU, memory, I/O)
  98 - Proper connection pooling and resource management
  99 - Scalability considerations for growing data volumes
 100 - Monitoring of resource utilization and bottlenecks
 101 - Optimization of concurrent access patterns
 102
 103 **Common Problems:**
 104 - Inefficient resource utilization
 105 - Poor connection management leading to resource exhaustion
 106 - Lack of scalability planning for data growth
 107 - Missing monitoring of resource utilization
 108 - Inefficient concurrent access patterns
 109
 110 **Evaluation Questions:**
 111 - Are database resources used efficiently?
 112 - Is connection pooling implemented properly?
 113 - Is the system designed for scalability?
 114 - Is resource utilization monitored and optimized?
 115 - Are concurrent access patterns optimized?
 116
 117 ## ISO/IEC Standards-Specific Criticism Process
 118
 119 ### Step 1: Query Performance Analysis
 120 1. **Analyze Query Efficiency**: Are queries optimized for performance?
 121 2. **Review Execution Plans**: Are execution plans analyzed and optimized?
 122 3. **Check Query Patterns**: Are common query patterns optimized?
 123 4. **Assess Performance Metrics**: Are performance metrics monitored?
 124
 125 ### Step 2: Indexing Assessment
 126 1. **Check Index Usage**: Are indexes used appropriately for query patterns?
 127 2. **Evaluate Index Selection**: Are indexes selected based on actual usage?
 128 3. **Assess Index Maintenance**: Is index maintenance performed regularly?
 129 4. **Review Index Performance**: Is index performance monitored and optimized?
 130
 131 ### Step 3: Schema Performance Analysis
 132 1. **Check Normalization**: Is normalization balanced with performance needs?
 133 2. **Evaluate Data Types**: Are data types chosen for efficiency?
 134 3. **Assess Storage Patterns**: Are storage patterns optimized for access?
 135 4. **Review Partitioning**: Is partitioning implemented for large tables?
 136
 137 ### Step 4: Execution Plan Analysis
 138 1. **Check Execution Plans**: Are execution plans analyzed for optimization?
 139 2. **Evaluate Join Strategies**: Are join strategies optimized?
 140 3. **Assess Access Methods**: Are access methods efficient?
 141 4. **Review Performance Testing**: Is performance testing performed?
 142
 143 ### Step 5: Resource Management Assessment
 144 1. **Check Resource Utilization**: Are database resources used efficiently?
 145 2. **Evaluate Connection Management**: Is connection pooling implemented?
 146 3. **Assess Scalability**: Is the system designed for scalability?
 147 4. **Review Monitoring**: Is resource utilization monitored?
 148
 149 ## ISO/IEC Standards-Specific Criticism Guidelines
 150
 151 ### Focus on Performance Optimization
 152 **Good Criticism:**
 153 - "The query plan shows a full table scan due to a missing index on the WHERE clause column."
 154 - "The use of a correlated subquery is inefficient; consider using a JOIN instead."
 155 - "The schema is over-normalized, causing excessive joins and poor performance."
 156 - "The execution plan indicates a poor join strategy that could be optimized."
 157
 158 **Poor Criticism:**
 159 - "This query is slow."
 160 - "This needs optimization."
 161 - "This is inefficient."
 162
 163 ### Emphasize Performance Best Practices
 164 **Good Criticism:**
 165 - "The missing index on column X causes full table scans for this query pattern."
 166 - "The correlated subquery in the SELECT clause can be replaced with a more efficient JOIN."
 167 - "The data type VARCHAR(255) is inefficient for this use case; consider a more appropriate type."
 168 - "The execution plan shows a nested loop join that could be optimized with proper indexing."
 169
 170 **Poor Criticism:**
 171 - "This is not optimized."
 172 - "This could be faster."
 173 - "This is inefficient."
 174
 175 ## ISO/IEC Standards-Specific Problem Categories
 176
 177 ### Query Performance Problems
 178 - **Inefficient Queries**: Poorly optimized queries with slow execution
 179 - **Missing Indexes**: Lack of indexes on frequently queried columns
 180 - **Poor Execution Plans**: Suboptimal execution plans leading to slow performance
 181 - **Inefficient Joins**: Poor join strategies causing performance issues
 182
 183 ### Indexing Problems
 184 - **Missing Indexes**: Lack of indexes on columns used in WHERE, JOIN, and ORDER BY clauses
 185 - **Redundant Indexes**: Duplicate or overlapping indexes
 186 - **Unused Indexes**: Indexes that are not used by any queries
 187 - **Poor Index Selection**: Indexes that don't match query patterns
 188
 189 ### Schema Performance Problems
 190 - **Over-Normalization**: Excessive normalization causing performance issues
 191 - **Poor Data Types**: Inefficient data type selection affecting storage and performance
 192 - **Missing Partitioning**: Lack of partitioning for large tables
 193 - **Inefficient Storage**: Poor storage patterns for frequently accessed data
 194
 195 ### Execution Plan Problems
 196 - **Poor Execution Plans**: Suboptimal execution plans leading to slow queries
 197 - **Missing Performance Analysis**: Lack of execution plan analysis and optimization
 198 - **Inefficient Access Methods**: Poor access methods for data retrieval
 199 - **Missing Performance Testing**: Lack of performance testing and benchmarking
 200
 201 ### Resource Management Problems
 202 - **Inefficient Resource Usage**: Poor utilization of database resources
 203 - **Connection Issues**: Inadequate connection management and pooling
 204 - **Scalability Problems**: Lack of scalability planning for data growth
 205 - **Missing Monitoring**: Lack of resource utilization monitoring
 206
 207 ## ISO/IEC Standards-Specific Criticism Templates
 208
 209 ### For Query Performance Issues
 210 ```
 211 Query Performance Issue: [Specific performance problem]
 212 Problem: [What makes this query inefficient]
 213 Impact: [Potential for slow queries, resource exhaustion, or scalability issues]
 214 Evidence: [Query plans, execution times, or performance metrics]
 215 Priority: [High/Medium/Low]
 216 ```
 217
 218 ### For Indexing Issues
 219 ```
 220 Indexing Issue: [Specific indexing problem]
 221 Problem: [What makes this indexing strategy inadequate]
 222 Impact: [Potential for slow queries due to missing or inefficient indexes]
 223 Evidence: [Query patterns, execution plans, or index usage statistics]
 224 Priority: [High/Medium/Low]
 225 ```
 226
 227 ### For Schema Performance Issues
 228 ```
 229 Schema Performance Issue: [Specific schema performance problem]
 230 Problem: [What makes this schema design inefficient]
 231 Impact: [Potential for poor performance due to schema design issues]
 232 Evidence: [Schema design, normalization level, or data type choices]
 233 Priority: [High/Medium/Low]
 234 ```
 235
 236 ### For Execution Plan Issues
 237 ```
 238 Execution Plan Issue: [Specific execution plan problem]
 239 Problem: [What makes this execution plan inefficient]
 240 Impact: [Potential for slow queries due to poor execution plans]
 241 Evidence: [Execution plan analysis, query performance metrics]
 242 Priority: [High/Medium/Low]
 243 ```
 244
 245 ### For Resource Management Issues
 246 ```
 247 Resource Management Issue: [Specific resource management problem]
 248 Problem: [What makes this resource usage inefficient]
 249 Impact: [Potential for resource exhaustion or scalability issues]
 250 Evidence: [Resource utilization metrics, connection patterns]
 251 Priority: [High/Medium/Low]
 252 ```
 253
 254 ## ISO/IEC Standards-Specific Criticism Best Practices
 255
 256 ### Do's
 257 - **Focus on Performance Metrics**: Use specific performance measurements and metrics
 258 - **Analyze Execution Plans**: Review and optimize query execution plans
 259 - **Consider Scalability**: Evaluate performance for growing data volumes
 260 - **Monitor Resource Usage**: Track and optimize resource utilization
 261 - **Document Performance Decisions**: Clearly document performance optimization decisions
 262
 263 ### Don'ts
 264 - **Ignore Performance Issues**: Don't overlook performance problems
 265 - **Accept Poor Performance**: Don't tolerate inefficient queries or schemas
 266 - **Skip Performance Testing**: Don't ignore performance testing and benchmarking
 267 - **Overlook Resource Usage**: Don't accept inefficient resource utilization
 268 - **Forget Monitoring**: Don't skip performance monitoring and analysis
 269
 270 ## ISO/IEC Standards-Specific Criticism Checklist
 271
 272 ### Query Performance Assessment
 273 - [ ] Are queries optimized for performance and scalability?
 274 - [ ] Are execution plans reviewed and optimized?
 275 - [ ] Are unnecessary full table scans avoided?
 276 - [ ] Are WHERE clauses optimized for efficient filtering?
 277 - [ ] Are aggregate functions used efficiently?
 278
 279 ### Indexing Assessment
 280 - [ ] Are indexes used appropriately to speed up data access?
 281 - [ ] Are indexes selected based on actual query patterns?
 282 - [ ] Are redundant or unused indexes identified and removed?
 283 - [ ] Is index maintenance performed regularly?
 284 - [ ] Is index usage monitored and optimized?
 285
 286 ### Schema Performance Assessment
 287 - [ ] Is normalization balanced with performance needs?
 288 - [ ] Are data types chosen for efficiency and storage optimization?
 289 - [ ] Are large objects handled efficiently?
 290 - [ ] Is partitioning implemented for large tables?
 291 - [ ] Are storage patterns optimized for access patterns?
 292
 293 ### Execution Plan Assessment
 294 - [ ] Are execution plans analyzed for optimization opportunities?
 295 - [ ] Are performance bottlenecks identified and addressed?
 296 - [ ] Is query performance monitored and tracked?
 297 - [ ] Are join strategies optimized for efficiency?
 298 - [ ] Is performance testing and benchmarking performed?
 299
 300 ### Resource Management Assessment
 301 - [ ] Are database resources used efficiently?
 302 - [ ] Is connection pooling implemented properly?
 303 - [ ] Is the system designed for scalability?
 304 - [ ] Is resource utilization monitored and optimized?
 305 - [ ] Are concurrent access patterns optimized?
 306
 307 ## ISO/IEC Standards-Specific Evaluation Questions
 308
 309 ### For Any SQL Code
 310 1. **Are queries optimized for performance and scalability?**
 311 2. **Are indexes used appropriately to speed up data access?**
 312 3. **Are execution plans reviewed and optimized?**
 313 4. **Is normalization balanced with performance needs?**
 314 5. **Are performance metrics monitored and addressed?**
 315 6. **Are database resources used efficiently?**
 316 7. **Is connection pooling implemented properly?**
 317 8. **Are performance bottlenecks identified and addressed?**
 318 9. **Is performance testing and benchmarking performed?**
 319 10. **Are concurrent access patterns optimized?**
 320
 321 ### For Database Schemas
 322 1. **Is normalization applied appropriately for performance requirements?**
 323 2. **Are indexes used to support query performance?**
 324 3. **Are data types chosen for efficiency and storage optimization?**
 325 4. **Is partitioning implemented for large tables?**
 326 5. **Are storage patterns optimized for access patterns?**
 327
 328 ### For Stored Procedures and Triggers
 329 1. **Are queries within procedures optimized for performance?**
 330 2. **Are execution plans analyzed for optimization opportunities?**
 331 3. **Is resource usage monitored and optimized?**
 332 4. **Are performance bottlenecks identified and addressed?**
 333 5. **Is performance testing performed for procedures?**
 334
 335 ### For Performance and Scalability
 336 1. **Are queries optimized for performance and scalability?**
 337 2. **Are indexes used appropriately to speed up data access?**
 338 3. **Are execution plans reviewed and optimized?**
 339 4. **Is normalization balanced with performance needs?**
 340 5. **Are performance metrics monitored and addressed?**
 341
 342 ## SQL Performance Principles Applied
 343
 344 ### "Optimize for Efficiency and Speed"
 345 - Design queries for optimal performance
 346 - Use appropriate indexes for query patterns
 347 - Analyze and optimize execution plans
 348
 349 ### "Balance Normalization with Performance"
 350 - Apply normalization appropriately for the use case
 351 - Consider performance implications of schema design
 352 - Optimize storage patterns for access patterns
 353
 354 ### "Monitor and Optimize Resources"
 355 - Use database resources efficiently
 356 - Implement proper connection management
 357 - Monitor and optimize resource utilization
 358
 359 ### "Plan for Scalability"
 360 - Design for growing data volumes
 361 - Consider concurrent access patterns
 362 - Implement performance testing and benchmarking
 363
 364 ### "Analyze and Optimize Execution"
 365 - Review execution plans for optimization opportunities
 366 - Identify and address performance bottlenecks
 367 - Monitor query performance metrics