SQL Server Migration to KingbaseES: A Performance Test of a Complex BI Query

After the SQL Server data migration entered the acceptance phase, the most frequent question from the business side was not whether the tables had been migrated, but whether the reports could still be used as before. Getting simple queries to run only proves that connections, objects, and basic syntax are fine. What truly weighs on the project team's mind are those BI queries that have been running for years: many tables, complex statistical logic, and SQL containing scalar subqueries, which flood in with massive concurrent requests at the beginning of each month or during business analysis meetings.

The target environment for this migration is KingbaseES V9R4C019. After object migration and data verification were completed, performance acceptance did not choose a deliberately simplified SQL. Instead, it retained a typical type of query from the reporting system: multi-table joins involving orders, order items, customers, regions, and product categories, with refund amounts calculated per order item. A single execution needs to handle detail aggregation, and the scalar subquery also references the outer order item ID, making it a good test of the execution efficiency and concurrency stability of complex queries after migration.

The stress test results are direct: under 100 concurrent users, the complex query TPS improved by 60% compared to before migration, and the average response time was about 1/10 of the original. BI reports that previously required long waits can now return results faster in the migrated environment. This result was not derived from a SELECT 1, but from a migration acceptance process that verified business SQL, result consistency, indexes, and statistics step by step.

Object Migration is Only the First Half

Migration from SQL Server to KingbaseES typically starts with tables, views, functions, stored procedures, data types, and the data itself. The KingbaseES SQL Server Migration Guide lists the migration environment, objects, and verification paths. These tasks address the question of "can it be migrated, can it run."

BI systems require an additional layer of performance acceptance. They often read not just a single table, but a set of fact tables and dimension tables. A single report page might issue multiple statistical SQL statements simultaneously, and during normal office hours, significant concurrency peaks occur. Even if every SQL statement in the migrated database returns correct results, the business side will still feel the migration is incomplete if queuing times increase under high concurrency.

Therefore, the acceptance path was divided into three parts: first, confirm the business logic of the original SQL Server queries; second, verify the migrated objects, types, and result sets; and finally, observe the execution plans, index hits, and concurrency performance on KingbaseES.

This arrangement has a very practical benefit: syntax compatibility, data consistency, and performance issues are not mixed together. When a query errors, address compatibility first; if results differ, check the data and business logic first; only after results are consistent does a comparison of TPS and response time become meaningful.

Fixing the Migration Baseline First

Queries before and after migration cannot be compared using just a single returned record. Reports use a complete time window, and order status, refund status, amount precision, and region classification must all remain the same. Time boundaries are particularly easy to overlook. datetime2 in SQL Server can retain finer time precision. After migrating to the target, parameter types and comparison methods must be unified; otherwise, orders on the boundary of the same second might fall into different result sets.

Amount fields also cannot be directly converted to floating-point numbers during comparison. The acceptance table retains amount precision as numeric(18,2), order counts use integers, and regions and categories use standardized codes after migration. Each dimension is first sorted by key, then a difference set is performed, and amounts are compared using a fixed number of decimal places. Differences found this way can be traced to specific orders or items and will not be obscured by formatting differences.

SELECT COUNT(*) AS row_count,
       SUM(gross_amount) AS gross_amount,
       SUM(refund_amount) AS refund_amount,
       SUM(net_amount) AS net_amount
FROM verify_report_kingbase
WHERE report_date = DATE '2026-07-01';

If the total row count is the same but amounts differ, further breakdown by region_name and category_name is needed. Migration tools handle data transfer and object conversion, but the business logic must still be proven by the report SQL itself.

Null refunds and zero refunds must also be confirmed separately: when there is no refund record, the result should be converted to zero by COALESCE; when there is a refund record with a zero amount, the detail should still be retained. Case sensitivity and trailing spaces in region codes and category codes can also affect grouping results. The migrated character types, collation rules, and cleansing logic need to be consistent with the original report logic. Only when all these details are aligned does the observed performance change represent a comparison of the same thing.

Keeping the Original Complex Query Intact

The report calculates statistics for paid orders by region and product category, while deducting successful refunds. The core data is distributed across six tables: bi_sales_order holds order master data, bi_sales_order_item holds product details, dim_product, dim_customer, and dim_region provide product, customer, and region dimensions, and bi_refund records refunds by order item.

The original query in SQL Server uses DATEADD, ISNULL, and a correlated scalar subquery. The refund amount needs to access the refund table again based on the current order item ID, making it more likely to amplify differences caused by execution plan choices than ordinary multi-table joins.

DECLARE @begin_time datetime2 = '2026-07-01 00:00:00';
DECLARE @end_time   datetime2 = DATEADD(day, 1, @begin_time);

SELECT q.region_name,
       q.category_name,
       COUNT(DISTINCT q.order_id)  AS order_count,
       SUM(q.gross_amount)         AS gross_amount,
       SUM(q.refund_amount)        AS refund_amount,
       SUM(q.gross_amount)
         - SUM(q.refund_amount)    AS net_amount
FROM (
    SELECT o.order_id,
           oi.order_item_id,
           r.region_name,
           p.category_name,
           oi.quantity * oi.sale_price AS gross_amount,
           ISNULL((
               SELECT SUM(rf.refund_amount)
               FROM bi_refund AS rf
               WHERE rf.order_item_id = oi.order_item_id
                 AND rf.refund_status = 'SUCCESS'
           ), 0) AS refund_amount
    FROM bi_sales_order AS o
    JOIN bi_sales_order_item AS oi
      ON oi.order_id = o.order_id
    JOIN dim_product AS p
      ON p.product_id = oi.product_id
    JOIN dim_customer AS c
      ON c.customer_id = o.customer_id
    JOIN dim_region AS r
      ON r.region_id = c.region_id
    WHERE o.order_status = 'PAID'
      AND o.pay_time >= @begin_time
      AND o.pay_time <  @end_time
) AS q
GROUP BY q.region_name,
         q.category_name
ORDER BY q.region_name,
         q.category_name;

The trouble with this type of SQL is not the number of lines, but that the execution path can easily become long. Orders are first joined with items, products, customers, and regions, then the refund scalar subquery retrieves values based on the outer order_item_id, and the outermost layer completes the aggregation by region and category. When data volume and concurrency increase, the number of accesses to the refund table, the join order, and the size of intermediate results all affect response time.

Preserving Business Semantics First, Then Rewriting for Set Operations

After migrating to KingbaseES, the first version of the SQL only makes necessary syntax adaptations. ISNULL is changed to COALESCE, time boundaries use TIMESTAMP literals, and other join and aggregation logic remains unchanged. The results obtained this way can be compared item by item with the SQL Server baseline.

WITH order_summary AS (
    SELECT o.order_id,
           oi.order_item_id,
           r.region_name,
           p.category_name,
           oi.quantity * oi.sale_price AS gross_amount,
           COALESCE((
               SELECT SUM(rf.refund_amount)
               FROM bi_refund AS rf
               WHERE rf.order_item_id = oi.order_item_id
                 AND rf.refund_status = 'SUCCESS'
           ), 0) AS refund_amount
    FROM bi_sales_order AS o
    JOIN bi_sales_order_item AS oi
      ON oi.order_id = o.order_id
    JOIN dim_product AS p
      ON p.product_id = oi.product_id
    JOIN dim_customer AS c
      ON c.customer_id = o.customer_id
    JOIN dim_region AS r
      ON r.region_id = c.region_id
    WHERE o.order_status = 'PAID'
      AND o.pay_time >= TIMESTAMP '2026-07-01 00:00:00'
      AND o.pay_time <  TIMESTAMP '2026-07-02 00:00:00'
)
SELECT region_name,
       category_name,
       COUNT(DISTINCT order_id)         AS order_count,
       SUM(gross_amount)                AS gross_amount,
       SUM(refund_amount)               AS refund_amount,
       SUM(gross_amount - refund_amount) AS net_amount
FROM order_summary
GROUP BY region_name,
         category_name
ORDER BY region_name,
         category_name;

After result verification is complete, the refund calculation is changed to pre-aggregation by order item. The scalar subquery originally embedded in the detail results is expanded into an independent set. The optimizer can then choose a more suitable join path between the refund summary results and the order details, and it also avoids repeatedly triggering refund accesses for the same detail row.

WITH refund_agg AS (
    SELECT order_item_id,
           SUM(refund_amount) AS refund_amount
    FROM bi_refund
    WHERE refund_status = 'SUCCESS'
    GROUP BY order_item_id
),
order_detail AS (
    SELECT o.order_id,
           oi.order_item_id,
           r.region_name,
           p.category_name,
           oi.quantity * oi.sale_price AS gross_amount,
           COALESCE(ra.refund_amount, 0) AS refund_amount
    FROM bi_sales_order AS o
    JOIN bi_sales_order_item AS oi
      ON oi.order_id = o.order_id
    JOIN dim_product AS p
      ON p.product_id = oi.product_id
    JOIN dim_customer AS c
      ON c.customer_id = o.customer_id
    JOIN dim_region AS r
      ON r.region_id = c.region_id
    LEFT JOIN refund_agg AS ra
      ON ra.order_item_id = oi.order_item_id
    WHERE o.order_status = 'PAID'
      AND o.pay_time >= TIMESTAMP '2026-07-01 00:00:00'
      AND o.pay_time <  TIMESTAMP '2026-07-02 00:00:00'
)
SELECT region_name,
       category_name,
       COUNT(DISTINCT order_id) AS order_count,
       SUM(gross_amount) AS gross_amount,
       SUM(refund_amount) AS refund_amount,
       SUM(gross_amount - refund_amount) AS net_amount
FROM order_detail
GROUP BY region_name,
         category_name
ORDER BY region_name,
         category_name;

Both before and after the rewrite, the same region, category, order count, sales amount, refund amount, and net amount must be returned. Results from both ends can be imported into two acceptance tables, and bidirectional difference sets and amount difference checks can confirm that the business logic has not changed.

SELECT region_name, category_name, order_count,
       gross_amount, refund_amount, net_amount
FROM verify_report_sqlserver
EXCEPT
SELECT region_name, category_name, order_count,
       gross_amount, refund_amount, net_amount
FROM verify_report_kingbase;

SELECT region_name, category_name, order_count,
       gross_amount, refund_amount, net_amount
FROM verify_report_kingbase
EXCEPT
SELECT region_name, category_name, order_count,
       gross_amount, refund_amount, net_amount
FROM verify_report_sqlserver;

Only when no records are returned in either direction does the performance comparison proceed. This avoids mistaking missed joins, omitted refunds, or time boundary changes for optimization gains in the pursuit of lower execution times.

Concurrency testing is also executed using the same query logic. Request parameters are fixed to the same report date, the number of connections is fixed at 100, and the returned columns do not change with the database switch. The stress test client only records successful requests and does not count connection failures, syntax errors, or requests with empty result sets as TPS. In addition to average response time, P95 and error rates also need attention to avoid a scenario where the average looks good, but a few slow requests consistently hold up the report page.

Checking Execution Plans, Indexes, and Statistics Together

The plan node names for the same complex SQL may differ between the two databases. During acceptance, there is no need for a word-for-word correspondence; it is more important to focus on the scan scope, join order, number of scalar subquery executions, intermediate result row counts, and sort/aggregation overhead.

KingbaseES can use execution plan analysis for the final query. ANALYZE will actually execute the SQL, and BUFFERS can supplement cache access information. This is suitable for verification in a test environment; the query overhead should still be evaluated before use in a production environment.

EXPLAIN (ANALYZE, BUFFERS)
WITH refund_agg AS (
    SELECT order_item_id,
           SUM(refund_amount) AS refund_amount
    FROM bi_refund
    WHERE refund_status = 'SUCCESS'
    GROUP BY order_item_id
)
SELECT o.order_id,
       oi.order_item_id,
       COALESCE(ra.refund_amount, 0) AS refund_amount
FROM bi_sales_order AS o
JOIN bi_sales_order_item AS oi
  ON oi.order_id = o.order_id
LEFT JOIN refund_agg AS ra
  ON ra.order_item_id = oi.order_item_id
WHERE o.order_status = 'PAID'
  AND o.pay_time >= TIMESTAMP '2026-07-01 00:00:00'
  AND o.pay_time <  TIMESTAMP '2026-07-02 00:00:00';

Indexes were not pursued with a "more is better" approach, but were set up around the report's filtering and join paths. The orders table first narrows the scope by status and payment time, orders and items are joined via order_id, and refunds are then matched to specific product items via order_item_id.

CREATE INDEX idx_bi_order_status_pay_time
    ON bi_sales_order (order_status, pay_time, customer_id, order_id);

CREATE INDEX idx_bi_order_item_order_product
    ON bi_sales_order_item (order_id, product_id, order_item_id);

CREATE INDEX idx_bi_refund_status_item
    ON bi_refund (refund_status, order_item_id);

ANALYZE bi_sales_order;
ANALYZE bi_sales_order_item;
ANALYZE bi_refund;
ANALYZE dim_customer;
ANALYZE dim_region;
ANALYZE dim_product;

The specific column order should still be based on data distribution and the execution plan. The combination here corresponds to the current report: order_status is an equality condition, pay_time is a range condition, order_id joins orders and items, and order_item_id joins items and refunds. After refreshing statistics, observing the gap between estimated and actual row counts can reduce plan fluctuations caused by insufficient statistics in the early stages of migration.

The query path on the left retains the characteristics of correlated scalar subqueries and multi-layer aggregation. The one on the right first aggregates refund data by order item, then joins it with the order detail results. The diagram expresses the SQL structure and acceptance focus, not a replica of a specific console execution plan. Actual judgment should still be based on the plan, row counts, and execution times returned by KingbaseES.

The Performance Difference Truly Emerges Under 100 Concurrent Users

Single-session testing can only reveal obvious full table scans or syntax issues. After a BI report goes live, the same analysis page might be opened by multiple departments simultaneously, and the database must also handle other queries. This acceptance test compared the complex query under 100 concurrent users, unifying the business data, statistical time window, returned fields, and result set logic.

Since the available materials did not provide server models or absolute execution times, the performance data uses normalized values from before migration to avoid fabricating non-existent millisecond figures:

Metric	SQL Server (Before Migration)	KingbaseES V9R4C019	Comparison Result
Complex Query TPS	1.00	1.60	60% improvement
Average Response Time	1.00	0.10	Approximately 1/10 of original
Concurrent Connections	100	100	Consistent baseline

The increase in TPS means more report queries can be completed within the same time period, and the decrease in response time directly changes the front-end waiting experience. Business analysis reports that were previously prone to long waits can return faster after migration, so business personnel no longer need to repeatedly refresh the page for the same report.

This set of figures is the migration acceptance result for this specific business query, this data scale, and this concurrency model. It cannot directly substitute for capacity assessments of other systems. What it can confirm is: after migrating SQL Server data to KingbaseES, complex BI queries did not stop at the "syntactically executable" level. Under conditions of consistent results, matching indexes, and complete statistics, V9R4C019 handled the actual analytical load involving scalar subqueries and multi-table joins, and achieved better concurrency performance.

The performance change also needs to be explained by returning to the SQL itself. The first compatible version of the SQL retained the correlated subquery to facilitate semantic verification before and after migration. After confirming the result set, the hot report adopted a pre-aggregation approach by order item, reducing repeated accesses to the refund table. This adjustment did not change the statistical meaning of the report but made the join relationships and aggregation boundaries clearer. The same applies to the handling of indexes and statistics: indexes serve time filtering and detail joins, and statistics help the optimizer estimate actual row counts. Both need to be verified through plans and concurrency results, not just judged by whether the DDL executed successfully.

Performance Acceptance Must Retain Verifiable Evidence

Before a database migration goes live, it is relatively easy to form checklists for compatibility rates, object counts, and data row counts, but performance is often glossed over with a simple "it was tested." For complex queries, it is best to retain at least five types of materials: the original SQL, the migrated SQL, result differences, execution plans, and concurrency metrics. When data volumes grow or report logic is adjusted later, re-verification can follow the same baseline instead of guessing where the problem lies again.

The improvement in this BI query came from the combined effect of several specific tasks: the migrated query maintained the original business semantics, the correlated scalar subquery was transformed into a set operation that could be uniformly optimized, indexes covered the time filtering and order join paths, and statistics were refreshed before the stress test. What KingbaseES V9R4C019 ultimately delivered was not just a "success" status in the migration tool, but a query environment capable of continuing to handle high-concurrency reports.

For an SQL Server data migration project, being able to query tables and connect programs is only a switching condition. The migration truly enters a deliverable state when complex SQL results remain unchanged, responses are stable under high concurrency, and business personnel no longer face long waits when opening reports.