All Docs
FeaturesCalmony Sanctions MonitorUpdated March 11, 2026

How We Eliminated the N+1 Query Pattern in Batch Rescreening

How We Eliminated the N+1 Query Pattern in Batch Rescreening

Release v0.1.72 · Performance · PERF-09

Background

The nightly sanctions screening sync is one of the most database-intensive operations on the platform. Every monitored individual must be rescreened against the latest OFSI consolidated list, and their match records and compliance status updated accordingly.

As the number of monitored people grows, the efficiency of this process matters a great deal — not just for job duration, but for database load during the sync window.

The Problem: N+1 Queries in the Rescreening Loop

Prior to this release, src/lib/batch-rescreen.ts processed monitored people one at a time in a sequential for-loop. For each person, the engine issued between 3 and 5 individual database operations:

for each person:
  1. DELETE pending matches WHERE personId = ?
  2. INSERT new matches (one per match)
  3. SELECT remaining matches WHERE personId = ?
  4. UPDATE person status WHERE personId = ?

This is the classic N+1 query pattern — the number of database round-trips scales linearly with the size of the monitored population. For 500 monitored people, a single nightly job could produce 1,500 to 2,500 sequential database round-trips. Each round-trip carries network and transaction overhead that compounds across the full population.

Separately, src/app/dashboard/page.tsx was awaiting 7 independent database queries in series on every page load, adding unnecessary latency for every compliance officer opening the dashboard.

The Fix: Set-Based Operations

Batch Rescreening Engine

The rescreening loop has been replaced with a set-based pipeline that operates on the entire monitored population in bulk:

Step 1 — Bulk fetch All pending match IDs for the entire monitored population are retrieved in a single query:

SELECT id, personId, matchData
FROM pending_matches
WHERE personId IN (...all monitored person IDs...)

Step 2 — In-memory diffing The new match sets (computed from the latest OFSI list) are compared against the existing match sets entirely in application memory. No further queries are needed to determine what has changed.

Step 3 — Batch delete All stale pending matches across the entire population are removed in a single statement:

DELETE FROM pending_matches
WHERE id IN (...all stale match IDs...)

Step 4 — Batch insert All new matches across the entire population are written in a single bulk insert.

Step 5 — Batch status update All person status changes are applied in a single operation.

The total number of database operations is now constant (O(1)) with respect to population size, rather than linear.

Dashboard Page

The 7 independent data-fetching calls on the dashboard page have been wrapped in Promise.all():

// Before — 7 sequential awaits
const stats        = await getStats();
const alerts       = await getAlerts();
const recentScans  = await getRecentScans();
// ... and so on

// After — all 7 run concurrently
const [
  stats,
  alerts,
  recentScans,
  // ...
] = await Promise.all([
  getStats(),
  getAlerts(),
  getRecentScans(),
  // ...
]);

Because none of these queries depend on one another's results, they can safely execute in parallel. Dashboard load time is now bounded by the slowest single query rather than the sum of all query durations.

Impact at a Glance

ScenarioBeforeAfter
Batch rescreen, 500 people1,500–2,500 DB round-trips~5 DB operations
Batch rescreen query complexityO(n)O(1)
Dashboard page DB queries7 sequential7 concurrent

What This Means in Practice

  • Nightly sync jobs complete faster, freeing database connections sooner and reducing the window during which the sync job competes with live traffic.
  • Dashboard page loads are faster for compliance officers, especially on instances with higher network latency to the database.
  • The platform scales more predictably as monitored populations grow — adding more people to monitoring no longer linearly increases job duration.

Files Changed

  • src/lib/batch-rescreen.ts — Rescreening engine refactored to use set-based bulk operations.
  • src/app/dashboard/page.tsx — Independent queries parallelised with Promise.all().

This change is part of the ongoing PERF series of performance improvements. No changes to the screening logic, match scoring, or compliance workflows were made in this release.