Reducing Target/Handling latency in Batch Optimized Apply

[Dsolisc]

Hi Community i need some advise to improve 2 task in Qlik replicate, below i give more details:

• Qlik Replicate: 2025.11 (Windows Failover Cluster, On-Premise Chile)
• Source: IBM DB2 for iSeries (On-Premise Chile)
• Target: Amazon Aurora PostgreSQL 17.7 (AWS us-east-1) — reached via Direct Connect (~90-130ms RTT)
• Mode: Batch Optimized Apply, 1 table per task

Problem

After tuning the batch settings (Apply batched changes - But less than from 30s → 2s, Force apply memory 500 → 100MB), latency spikes (up to 35s) disappeared. But a stable floor of ~6s remains.

Performance log shows the latency is entirely on the target side:

Source latency 0.38s  |  Target latency 6.53s  |  Handling latency 6.14s

We confirmed the bottleneck is not the source (0.4s), not Aurora resources (idle, DB Load 0.27/8 vCPU, dominant wait = Client:ClientRead), and not the Qlik server (CPU ~0%). The ~6s is spent in the apply cycle (COPY → UPDATE → TRUNCATE/DROP of the Net Changes table) over the cross-continent link.  

With a single-table-per-task Batch Optimized Apply over a high-RTT (~100ms) link to Aurora PostgreSQL, how can we reduce the target/handling latency floor? Specifically:

1. Is there an internal parameter to reuse the attrep_changes Net Changes table (TRUNCATE only) instead of DROP+CREATE each cycle, to cut round-trips?
2. Can apply-cycle SQL operations be pipelined/batched to reduce the number of network round-trips per cycle?
3. For low-latency over high-RTT links, would Transactional Apply (min changes=100, max time=1s) outperform Batch Optimized Apply?
4. Any recommended internal parameters for high-latency target connections?

any advice is welcome

Regard

[john_wang]

Hello @Dsolisc ,

The Net Change table attrep_changes is an intermediate table used by the Batch Optimized Apply mode. It is created when the first change is captured from the source database and is truncated between batches.

Under normal circumstances, the Net Change table is not repeatedly dropped and recreated. It is only recreated when necessary, for example, if a captured DDL operation changes the source table structure and the Net Change table metadata needs to be refreshed to match the new schema. In addition, the table is dropped when the Replicate task stops and is automatically recreated the next time the task starts.

In short, the lifecycle of the Net Change table is by design, and there is no option to disable its automatic recreation when it is required.

If you observe that the Net Change table is being dropped and recreated frequently during task execution, please set SOURCE_CAPTURE/TARGET_APPLY to Verbose, reproduce the issue, and review the task log files. The verbose logs should help identify what is triggering the table recreation.

Also, based on what you've described, it appears that your environment has relatively high network latency (high RTT). In such environments, Batch Optimized Apply is generally the preferred mode. However, I would still recommend evaluating the performance of Transactional Apply in your environment to determine whether it better meets your workload and performance requirements.

BTW, I noticed that your current design uses single-table-per-task. I'm not sure how many tasks or tables you have in total, however, the tasks do impact each other by competing for the same system resources. You may want to try running only a few tasks as a test to see whether the overall performance improves. This would help us estimate whether resource contention is a contributing factor.

Hope this helps.
John.

[Rahul_Kale]

Hello @Dsolisc,

Thank you for reaching out to the Qlik community.
 

Reuse attrep_changes table: Not possible. It’s internally managed and recreated as needed; no parameter to force TRUNCATE-only.

Reduce round-trips: Only real lever = fewer apply cycles. Increase, but less than (e.g. 5–10s) and batch size (memory).
 

Transactional Apply: Worse over high RTT. Executes row-by-row; batch is much faster.
 

Internal latency tuning knobs: None for pipelining SQL Batch apply already minimizes statements.
 

Biggest improvement: Reduce RTT, Move Replicate closer to Aurora (same AWS region). 

Key takeaway: Your ~6s floor = fixed per-cycle overhead + 100ms RTT. Reduce cycle frequency or reduce distance.