Payment Transaction Optimization

Results

What it did

The problem

Why it needed building

Stored-procedure complexity was dragging payment throughput and making the system hard to support. Per-row cursors and nested procedures meant the database scanned far more than it needed to, slowing daily financial processing and raising the operational risk.

The approach

How it works

Reworked the hot transaction paths from row-by-row cursors into set-based operations, cut redundant scans, and stabilized the procedures so daily processing became faster and more predictable to operate.

1. 01
   Profile Hot PathsIdentify the procedures dominating execution time
2. 02
   Set-based RewriteReplace per-row cursors with single-pass operations
3. 03
   Reduce ScansCut redundant reads and tighten access patterns
4. 04
   StabilizeHarden procedures for predictable daily runs

Live demo

Try it yourself

-- per-row cursor over transactions
DECLARE cur CURSOR FOR
  SELECT id FROM txn WHERE batch = @b;
WHILE @@FETCH_STATUS = 0
  EXEC usp_ProcessTxn @id;  -- nested proc / row

-- set-based, single pass
UPDATE t
  SET status = s.status, settled = s.ts
FROM txn t
JOIN staged_results s ON s.id = t.id
WHERE t.batch = @b;

Tech stack

Built with