A Complex BI Query Ran 10x Faster After Moving from SQL Server to KingbaseES
Database migration projects routinely check that tables and objects transfer correctly, but BI teams care about whether month-end reports still return fast under load. This test provides a concrete, repeatable acceptance pattern—semantic verification first, then query rewriting, then concurrency testing—that catches the real failure mode: correct results that arrive too slowly when dozens of users hit the same report.
A database migration from SQL Server to KingbaseES V9R4C019 was stress-tested with a production BI query joining orders, items, customers, regions, products, and refunds. The query retained a correlated scalar subquery in its first compatible version to verify semantic equivalence, then was rewritten to pre-aggregate refunds by order item, eliminating repeated table access. Under 100 concurrent connections, TPS rose 60% and average response time dropped to roughly one-tenth of the original.
The acceptance process separated three concerns: syntax compatibility, data consistency, and performance. Result sets were compared using bidirectional EXCEPT queries and fixed-precision numeric comparisons to ensure the business logic remained identical. Indexes were designed around the query's actual filter and join paths—order status and pay time for the orders table, order_id for the order-to-item join, and order_item_id for the refund join—and statistics were refreshed before testing.
Execution plans were analyzed with EXPLAIN (ANALYZE, BUFFERS) rather than compared node-by-node. The key change was replacing the correlated subquery with a CTE that aggregates refunds once, letting the optimizer choose a more efficient join path. The project retained original SQL, migrated SQL, result differences, execution plans, and concurrency metrics as verifiable evidence for future re-validation.
Most migration acceptance stops at row counts and object lists; performance under concurrency is the part that gets hand-waved as 'tested' and causes the first production incident.
Keeping the correlated subquery in the first migrated version is a disciplined move—it decouples semantic verification from optimization, so you never mistake a missing join for a speed improvement.
Pre-aggregating the refund data before joining eliminated a per-row subquery execution, which is the kind of rewrite that matters far more than syntax-level tuning when concurrency climbs.
Normalizing performance results to a baseline of 1.00 avoids fabricating absolute numbers when hardware specs aren't disclosed, but it also makes the 10x improvement claim impossible to independently reproduce without the original environment.