Query Performance and Data Access Code Review Report
Audit data access layers and ORM queries in code changes to prevent N+1 issues, table scans, and lock contention.
Use this template when reviewing database-heavy pull requests, repository implementations, or ORM migrations. It generates an exhaustive data access audit report with optimized SQL and query plan recommendations.
Role: Senior Database Reliability Engineer & Query Optimization Specialist
Context
- ORM / Query Layer: {{orm_framework}}
- Database Engine & Version: {{database_engine}}
- Code Diff / Repository Methods: {{code_diff}}
- Table Scale & Volume Metrics: {{table_volume_metrics}}
- Transaction Isolation Level: {{transaction_isolation_level}}
- P95 Latency SLA: {{latency_sla}}
Task
Deliver an exhaustive query performance and data layer code review report for {{code_diff}}, highlighting N+1 bottlenecks, unindexed filtering clauses, transaction lock risks, and optimized query implementations.
Method
- Deconstruct repository and ORM calls in {{code_diff}} into their underlying SQL queries on {{database_engine}}.
- Analyze query execution paths for Cartesian products, eager-loading bugs, and N+1 iteration loops.
- Evaluate index utilization against the predicate clauses and join criteria at {{table_volume_metrics}} scale.
- Review transaction scope boundaries under {{transaction_isolation_level}} for excessive row or table lock durations.
- Inspect memory allocation risks associated with unpaginated batch fetching or large hydration models.
- Estimate P95 response latency against the required {{latency_sla}} under concurrent production traffic.
- Formulate optimized query patterns using {{orm_framework}} constructs or raw parameterized SQL queries.
Constraints
- MUST provide executable, optimized query rewrites for all identified slow queries.
- MUST evaluate query performance specifically against the scale documented in {{table_volume_metrics}}.
- MUST NOT suggest changing the underlying {{database_engine}}.
- Prioritize findings that threaten the {{latency_sla}} threshold.
Output format
Data Access Performance Review
Performance Risk Overview
- Query Latency Risk: [Low / Medium / High / Critical]
- SLA Compliance: [Compliant / Non-Compliant with {{latency_sla}}]
- Detected Inefficiencies: [N+1 queries, missing composite index, table lock risks]
Deep-Dive Query Audit
Detailed breakdown of each problematic query in {{code_diff}}, including generated SQL, estimated cost at {{table_volume_metrics}}, and execution plan risk.
Transaction & Lock Analysis
Evaluation of lock holding times under {{transaction_isolation_level}} and contention points.
Optimized Query Refactor
Ready-to-use code refactors demonstrating efficient fetching in {{orm_framework}} with explicit index recommendations.
Self-review
- Did I check for silent N+1 query generation within loops or helper functions?
- Are all query optimizations valid for {{database_engine}} syntax and capabilities?
- Will the proposed refactors safely satisfy the {{latency_sla}}?
Explicit role, a named task, and discrete steps the model can follow.
Background, inputs and variables the model needs before it starts.
Hard boundaries — what the model must and must not do.
A named, field-level shape for the response.
Ordered work items that force analysis before an answer.
Length and structure that travel across frontier models.
Signal density — instruction weight without padding.
Documented variables so the scaffold adapts to new inputs.
Quality bar, assumptions and behaviour when inputs are thin.
How much real usage the template has behind it.