How can we reduce cross-region write latency in a legacy .NET 4.8 application with row-by-row SQL operations?

The most effective design is to execute each bulk command in Region A near the writable primary, then incrementally replace row-by-row database calls with a small number of dependency-aware, transactional batches.

Cross-region communication is inherently slower than intra-region communication, so no SQL Server upgrade can remove the latency multiplied by hundreds of synchronous round trips.^1^

Recommended target flow

1. The regional application sends the complete business request once to a write-execution component in Region A.
2. That component executes the existing business operation against the primary over a low-latency regional connection.
3. Within selected high-volume operations, it sends rows using table-valued parameters (TVPs), SqlBulkCopy, or SqlDataAdapter batching.
4. It returns one consolidated result containing success/failure information, generated keys, counters, and validation errors.

This does not require moving reads: read-only requests can continue using local readable replicas. Always On has one read-write primary; secondary databases remain read-only.^2^

If the caller does not require an immediate result, place requests on a durable queue and process them with bounded concurrency in Region A. Queue-based load leveling protects the primary from bursts, but changes the operation to asynchronous completion and therefore requires idempotency, status tracking, and retry handling.^3^

Incremental data-access changes

1. Batch independent statements first

Start with audit inserts, detail inserts, and independent updates. TVPs send multiple strongly typed rows in one parameterized command without multiple round trips.^4^

CREATE TYPE dbo.WorkItemBatch AS TABLE
(
    ClientRowId int PRIMARY KEY,
    EntityId int NULL,
    Value nvarchar(200) NULL
);

using (var cmd = new SqlCommand(
    "INSERT dbo.WorkItem(EntityId, Value) " +
    "SELECT EntityId, Value FROM @Rows;", connection, transaction))
{
    var p = cmd.Parameters.Add("@Rows", SqlDbType.Structured);
    p.TypeName = "dbo.WorkItemBatch";
    p.Value = rowsDataTable;
    cmd.ExecuteNonQuery();
}

A stored procedure is optional: a TVP can be consumed by parameterized SQL text, allowing migration through the common data-access layer rather than converting the whole application.

2. Preserve dependencies with ordered phases

Do not defer every command blindly. Partition an operation at dependency barriers:

1. Batch validations that can be expressed as joins against the incoming TVP.
2. Allocate or insert parent records.
3. Return generated values mapped to a caller-supplied ClientRowId.
4. Populate dependent child batches with those values.
5. Execute counter updates, audit writes, and final validations.
6. Commit once.

Use OUTPUT INSERTED... to return identity or computed values for all affected rows.^5^ Do not assume output row order matches input order; correlate through an explicit client key. Also note that OUTPUT values reflect rows before triggers execute, so trigger-produced values that must be returned require a subsequent server-side query inside the same transaction.

3. Use SqlBulkCopy selectively

For large insert-only sets, bulk copy into a staging table, then perform validation and set-based DML from staging. This allows the existing triggers on final tables to run during the final INSERT, rather than relying on staging-table behavior.

If copying directly to final tables, explicitly choose required semantics. FireTriggers fires insert triggers, CheckConstraints checks constraints, and an existing SqlTransaction can enclose the copy.^6^ Test this path carefully because bulk execution can differ from the current statement-by-statement trigger cadence.

4. Use UpdateBatchSize only where DataTables already exist

For existing DataSet/DataTable update paths, SqlDataAdapter.UpdateBatchSize groups row commands into fewer round trips. Values greater than 1 enable batching; associated commands must use UpdatedRowSource.None or OutputParameters.^7^

This is not a transparent solution for arbitrary ExecuteScalar/ExecuteReader calls or commands whose next SQL text depends on the preceding result.

Transaction and rollout controls

• Keep each logical bulk request in one explicit, short transaction where current behavior requires atomicity. Transactions reduce log-flush overhead but do not eliminate individual network round trips.^8^
• Begin with one measurable operation and add a batch-specific repository method beside the existing row method.
• Compare old and new paths for affected rows, generated IDs, trigger effects, audit records, rollback behavior, lock duration, and replica lag.
• Roll out behind a feature flag, with bounded batch sizes and fallback to the original path.
• Measure commands and network round trips per request, p50/p95 duration, transaction duration, blocking, log growth, and AG send/redo queues.

SQL Server 2025

SQL Server 2025 does not introduce a feature that automatically combines a chatty ADO.NET workload. Its optimized locking can reduce blocking and lock memory for concurrent transactions, but it is disabled by default on SQL Server 2025 and requires accelerated database recovery before it can be enabled.^9^ Treat this as a concurrency improvement after reducing round trips, not as the primary latency solution.

Persisted statistics on readable secondaries can improve local read workloads, and AG communication enhancements can help availability behavior, but neither changes the requirement that writes execute on the primary.^10^

References

1. Using availability zones and regions
2. What is an Always On availability group?
3. Queue-Based Load Leveling pattern
4. Table-Valued Parameters
5. OUTPUT clause (Transact-SQL)
6. SqlBulkCopyOptions Enum-Definition
7. SqlDataAdapter.UpdateBatchSize Property-Definition
8. How to use batching to improve application performance
9. Optimized locking
10. What's new in SQL Server 2025