The 11-hour nightly rebate job
Tailspin's nightly rebate accrual job covers 480,000 customer/item combinations and must finish inside a six-hour window; it currently runs for 11 hours. The X++ uses a while select over the 480,000 rows, issues a separate select on CustTable inside the loop, and calls changecompany for every row because accruals are written to a shared company. Each record is written with update(), the whole run is wrapped in one ttsbegin/ttscommit, and an info() message is written per record. If the job aborts, nothing is committed and a rerun duplicates accrual journal lines. The team's proposed fix is to raise the batch task count from 1 to 16.
1. Diagnose the runtime and describe the redesign, ordered by impact.
Read the worked answer
The 11 hours are structural, not a tuning problem:
- One tts across 480,000 rows - the transaction log grows for the entire run, locks are held and escalate, and nothing is visible until the end. This alone is usually the dominant cost.
- N+1 queries - a select on CustTable per iteration; replace with a
while select ... joinor a set-based statement. - changecompany per row - each switch flushes and rebuilds the company cache and invalidates buffers; batch by company (one loop per company, not one switch per record).
- info() per record - each message is written to the batch log and Infolog, and batch history grows unboundedly; replace with counts at the end.
Redesign order: (1) do the work set-based - update_recordset/insert_recordset where per-row logic is absent, otherwise a joined while select with RecordInsertList for inserts; (2) commit in chunks (say 5,000 rows) with ttsbegin/ttscommit per chunk; (3) hoist the company loop and the customer join; (4) only then consider batch parallelism.
2. Should the team raise the batch task count to 16? Address that together with rerun safety.
Read the worked answer
Not first, and probably not 16. Parallelism only helps if tasks are independent and do not contend: 16 tasks writing accrual lines for the same items and the same shared company will block each other on the same rows and indexes, and SQL Server will serialise much of the work while adding deadlock risk. Partition the data explicitly (by legal entity, item group, or a range of RecIds), let each batch task own a disjoint partition, and scale the count to the number of partitions rather than to a round number.
Rerun safety has to be designed in, because the current design cannot be restarted:
- Introduce a run header (RunId, period, status) and chunk/partition records marked Pending/Processing/Done.
- Delete or upsert the accrual lines for the period before inserting, keyed on a natural key (RunId + period + item), with a unique index so a duplicate is impossible rather than unlikely.
- Commit the chunk's data and its Done flag in the same transaction, so a crash leaves a clean resume point.
- A rerun then skips Done partitions, which makes the 16-task version safe as well.
3. How would you test the change so the team can trust it before the next nightly window?
Read the worked answer
Three levels, each answering a different question:
- Correctness - a SysTest suite for the calculation against hand-built data: boundary periods, zero-quantity items, items with no customer record, and a company with no rows. Assert on the produced accrual amounts, not on whether the job ran.
- Idempotency - the highest-value test for this defect: run the job twice on the same period in a unit test and assert the line count and totals are identical. Then simulate a mid-run abort (throw inside a chunk) and assert that a resume produces the same result as a clean single run. This is cheap in SysTest and would have caught the original bug.
- Scale and regression - run in a Tier-2 sandbox restored from production data with a real timed window, comparing runtime and SQL Server profiler/trace duration before and after; then promote only if the window is met with headroom. Keep the SysTests in the pipeline so a later change cannot silently reintroduce a per-row commit or a duplicate insert.