SQL Performance Copilot: Multi-Dialect Query Execution Plan Analyzer
Developed to support Healthcare Business Consulting and clinical Data Warehouse engineering. Parses XML/JSON execution plans and AWR/DMV telemetry across Oracle, Microsoft SQL Server, PostgreSQL, and IBM DB2, detecting sargability violations, missing covering indexes, key lookups, and cardinality estimation skews, while generating deterministic rewrite patches.
Healthcare Data Warehouse Bottlenecks
Hospital Information Systems (HIS), Emergency Department tracking boards, and regional healthcare ETL pipelines process tens of millions of clinical episodes daily. Slow SQL queries degrade clinician UX and delay nightly regulatory data marts.
Multi-Dialect Optimization Benchmarks
Evaluated on production-scale clinical and administrative query workloads:
| Database Engine | Anti-Pattern Detected | Recommended Transformation | Latency Before | Latency After |
|---|---|---|---|---|
| SQL Server 2019 | Implicit `CONVERT_IMPLICIT` + Key Lookup | Sargable predicate + Covering Index `INCLUDE` | 14.8 s | 180 ms |
| Oracle 19c | Correlated Subquery in `SELECT` list | Hash Join Window Function (`ROW_NUMBER`) | 42.0 s | 1.15 s |
| PostgreSQL 16 | Non-indexed JSONB path scan | GIN expression index + materialized CTE | 6.4 s | 45 ms |
| IBM DB2 | Unbounded range scan on timestamp | Partition-pruned `BETWEEN` window | 28.5 s | 890 ms |
*Table 1: Multi-Dialect SQL Tuning Benchmarks in Healthcare Environments*
-- Example: Sargable Temporal Rewrite & Covering Index Recommendation
CREATE NONCLUSTERED INDEX IX_EpisodiPS_DataAccettazione_Covering
ON dbo.EPISODI_PRONTO_SOCCORSO (DATA_ACCETTAZIONE ASC, COD_PRESIDIO ASC)
INCLUDE (ID_PAZIENTE, COD_TRIAGE, ESITO_DIMISSIONE, MINUTI_ATTESA);Key Capabilities
- AST Sargability Verifier: Flags functions wrapping indexed columns in `WHERE` and `JOIN` clauses. - Cardinality Skew Detector: Identifies discrepancies between estimated and actual row counts that trigger suboptimal Nested Loops or TempDB spills. - Safe DDL Generator: Outputs online index creation scripts with `ONLINE = ON` and `SORT_IN_TEMPDB = ON` for zero-downtime hospital maintenance windows.