The Iterative Modify Tax: Replacing Loop-Based Updates with Set-Based Operations.
Imagine a night-end batch job that marks 2,000 processed staging records as archived or updates a custom processing flag across open sales lines.A common pattern is to fetch the record set, loop through with repeat…until, set the field value, and call Record.Modify(false).While setting RunTrigger to false bypasses AL event code, it still incurs a significant database penalty when wrapped inside a loop.
⚠️ The Problem: Row-by-Row SQL Round-Trips
Executing Record.Modify(false) inside a loop causes the Business Central server (NST) to send an individual SQL UPDATE statement to SQL Server for every single record.For 2,000 records, the application executes 2,000 separate SQL queries over the network connection. SQL Server must process each update request individually, leading to accumulated network latency and increased SQL lock hold times.
🛠️ The Fix: Set-Based Updates with ModifyAll
When field updates do not require AL field validation or table trigger logic, use Record.ModifyAll(Field, Value, false).Instead of issuing N queries, ModifyAll instructs the platform to generate a single SQL UPDATE statement with a WHERE clause matching your current AL filters. SQL Server executes the entire update as a set-based operation directly in the database engine.
Why it matters
➡️ Query Volume Reduction: Replaces N individual database statements with 1 set-based query.
➡️ Network & Throughput Impact: Eliminates ping-pong network latency between the NST and SQL Server, drastically lowering elapsed execution time.
➡️ Shorter Transaction Duration: Reduces the overall window during which database resources remain locked.
☣️ The Trap: Bypassing Triggers & Validation
Passing false to ModifyAll bypasses OnValidate triggers and OnBeforeModify / OnAfterModify events. Do not use ModifyAll on core business tables (like Item or Gen. Journal Line) if downstream code or extension subscribers rely on validation logic or event triggers to maintain data integrity.
💡 The Bottom Line
If an update doesnt require field validation or events, dont update row-by-row. Let SQL Server handle it in a single set-based operation.