Query Performance Optimization Audit for Recursive Math Pipelines

Role: Principal Database Performance Engineer specialized in distributed analytics engines and algorithmic query optimization.

Context

• Target Database Engine: {{database_engine}}
• Scale and Volume of Input Datasets: {{dataset_scale}}
• Service Level Latency SLA: {{target_latency_sla}}
• Core Calculation / Recursion Logic: {{complex_query_logic}}
• Current Table Physical Schema & Indexing: {{indexing_architecture}}
• Reported Hardware or Runtime Bottlenecks: {{resource_bottlenecks}}

Task

Generate a comprehensive, itemized pre-production audit checklist to evaluate, profile, and optimize compute-heavy SQL logic implementing mathematical recursions, graph calculations, or multi-frame window aggregations for {{target_latency_sla}} adherence.

Method

1. Analyze the mathematical mechanics in {{complex_query_logic}} to identify computational complexity hotspots (e.g., O(N^2) Cartesian products, recursion depth).
2. Audit anchor member definition and recursive term termination criteria to guarantee monotonic convergence.
3. Evaluate partition and order clauses in window frames against existing indices in {{indexing_architecture}} to identify spill-to-disk risks.
4. Screen for un-pruned partition scans across {{dataset_scale}} and verify predicate pushdown behavior in {{database_engine}}.
5. Inspect join cardinalities within iterative loops to pinpoint uncontrolled row multiplication and skew.
6. Formulate precise verification checks for memory buffer allocation, tempdb usage, and broadcast hash join thresholds addressing {{resource_bottlenecks}}.
7. Establish exact validation steps for deterministic tie-breaking in ordering windows.
8. Produce actionable remediation guidance for each identified optimization check item.

Constraints

• Every checklist item MUST include a distinct verification mechanism (e.g., EXPLAIN PLAN operator check, metric threshold, or SQL snippet).
• MUST categorize items strictly by query lifecycle: Logical Construction, Physical Access, and Memory/Execution Profiling.
• MUST NOT recommend generic database configuration changes outside the query and index definition scope.
• All items must be tailored specifically to {{database_engine}} execution mechanics.

Output format

• Executive Optimization Overview (max 150 words)
• Section 1: Algorithmic & Recursive Termination Checks (4-6 markdown checklist items with verification criteria)
• Section 2: Physical Access & Index Alignment Checks (4-6 markdown checklist items with EXPLAIN verification notes)
• Section 3: Memory, Partitioning & Concurrency Checks (4-6 markdown checklist items addressing {{resource_bottlenecks}})
• Triage & Remediation Matrix (table with columns: Check Item, Failure Indicator, Optimization Fix)

Self-review

• Confirm all 6 context variables are actively integrated into the checklist criteria.
• Verify that recursion termination edge cases are explicitly addressed.
• Ensure no placeholder text or vague recommendations are present.