Performance Deep Dive: Eliminating N+1 Queries in Bulk Demand Generation

Release: v1.0.63 · Control: PERF-09 · File: src/lib/routers/serviceCharge.ts

Background

Service charge billing in a multi-unit development involves generating demand notices for every unit in a development — potentially hundreds of records — in a single mutation call. Prior to v1.0.63, three mutations in the service charge router contained classic N+1 and nested-loop database anti-patterns that caused query counts to scale linearly (or worse) with the number of units and instalments.

For a typical 100-unit development on quarterly billing, a single bulk demand run could produce 400+ individual database roundtrips. Under load, or when processing multiple developments in parallel, this became a meaningful source of latency and database contention.

---

The Problems

1. generateDemands — O(2n) Roundtrips

For each unit in unitRows, the mutation was doing two things inside the loop:

// BEFORE — called once per unit inside the loop
const ref = await nextDemandRef(db, orgId); // COUNT query
await db.insert(serviceChargeDemands).values(demandRow); // INSERT

nextDemandRef() issues a COUNT(*) query against service_charge_demands to determine the next sequence number for the year. Because this ran inside the loop, every iteration incurred a read and a write roundtrip — 2 × n total queries for n units.

2. generateInstalmentDemands — O(n × m) Roundtrips

Instalment-based billing adds a second dimension. The mutation looped over units, and for each unit looped over instalments:

// BEFORE — nested loop, one INSERT per instalment per unit
for (const unit of units) {
  for (const instalment of instalments) {
    await db.insert(serviceChargeDemands).values(row);
  }
}

For 100 units × 4 quarterly instalments, this is 400 sequential INSERT statements.

3. issueDemandsBulk — O(2n) Update Roundtrips

When issuing a batch of draft demands, the mutation iterated and updated each demand individually:

// BEFORE — two updates per demand
for (const demand of draftDemands) {
  await db.update(serviceChargeDemands).set({ status: 'issued' }).where(...);
  await db.update(serviceChargeDemands).set({ issuedAt: now }).where(...);
}

This produced 2 × n update roundtrips for n demands.

---

The Fixes

Fix 1: Pre-compute the Sequence Range, Then Bulk Insert

Instead of calling nextDemandRef() per unit, a single query now determines the starting sequence number for the entire batch before the loop begins:

// AFTER — one query before the loop
const { start_seq } = await db
  .select({ start_seq: sql`COUNT(*) + 1` })
  .from(serviceChargeDemands)
  .where(
    and(
      eq(serviceChargeDemands.orgId, orgId),
      sql`EXTRACT(YEAR FROM created_at) = ${year}`
    )
  )
  .then(r => r[0]);

// Refs are now computed locally — zero additional DB calls
const allDemands = unitRows.map((unit, i) => ({
  ...buildDemandRow(unit),
  ref: formatRef(orgId, year, start_seq + i),
}));

// One bulk insert for the entire batch
await db.insert(serviceChargeDemands).values(allDemands);

Roundtrip reduction: O(2n) → O(2) (one COUNT, one INSERT).

Fix 2: Flatten the Instalment Loop into a Single Bulk Insert

// AFTER — collect all rows first, insert once
const allDemands: NewServiceChargeDemand[] = [];

for (const unit of units) {
  for (const instalment of instalments) {
    allDemands.push(buildInstalmentRow(unit, instalment));
  }
}

await db.insert(serviceChargeDemands).values(allDemands);

Roundtrip reduction: O(n × m) → O(1).

Fix 3: inArray Bulk Update for Issue Workflow

// AFTER — single UPDATE across all demand IDs
const allIds = draftDemands.map(d => d.id);

await db
  .update(serviceChargeDemands)
  .set({ status: 'issued', issuedAt: new Date() })
  .where(inArray(serviceChargeDemands.id, allIds));

Roundtrip reduction: O(2n) → O(1).

---

Performance Impact

Mutation	Scenario	Roundtrips Before	Roundtrips After
generateDemands	100 units	~200	~2
generateInstalmentDemands	100 units × 4 instalments	~400	~1
issueDemandsBulk	100 demands	~200	~1

For the worst-case scenario (instalment billing across a full 100-unit development), the total roundtrip count for a complete billing cycle drops from ~600+ queries to fewer than 10.

---

Compatibility

These are internal implementation changes only. There are no changes to:

• The GraphQL mutation signatures for generateDemands, generateInstalmentDemands, or issueDemandsBulk
• The shape or content of generated demand records
• Demand reference number formats or sequencing logic
• Any billing rules or financial calculations

Existing integrations and UI workflows are unaffected.