How to Optimize Performance and Data Consistency When Extending Business Central with AL for High-Volume Transactions?

[Mark Mckinney]

Hi Community,

I’m currently working on extending Microsoft Dynamics 365 Business Central (cloud, latest version) using AL, where we handle high-volume transactional data (50k+ records/day). I’m facing challenges related to performance degradation and data consistency, especially when implementing custom logic in posting routines and integrating with external APIs.

Here are the specific concerns:

• Long execution times when processing batch jobs (e.g., posting sales invoices with custom validations).
• Database locking issues, leading to occasional deadlocks when multiple users/processes run simultaneously.
• Maintaining data integrity while using temporary tables and background sessions.
• Best practices around event subscribers vs direct code modifications for scalability.
• Efficient use of SetAutoCalcFields vs CalcFields in large record loops.

 

[ChatGPT]

Hi Mark,
For 50k+ transactional records/day, the main rule is: keep posting transactions short, deterministic, and database-focused; move slow validation, external API calls, and enrichment outside the posting transaction wherever possible.

Recommended architecture​

1. Split the process into stages.
   • Stage inbound or batch data in a custom queue/staging table.
   • Validate in batches before posting.
   • Post only records that are already validated.
   • Send external API notifications after posting, not during posting.
2. Avoid synchronous external API calls inside posting routines.
   • HttpClient calls block the AL session until the external service responds.
   • If the API is slow, throttled, or down, your posting transaction stays open longer.
   • Use a queue table with statuses such as Pending, In Progress, Posted, Failed, and Retry After.
3. Use idempotency for integrations.
   • Store an external correlation ID or request ID.
   • Before posting or calling an external API, check whether the operation was already completed.
   • This prevents duplicate invoices or duplicate API messages after retries.
4. Use Job Queue or scheduled tasks for heavy work.
   • Use Job Queue when you need logging, retries, recurrence, and operational visibility.
   • Use TaskScheduler.CreateTask for queued background work that can run on another server.
   • Use StartSession only when you need immediate background work and understand that it runs in a separate transaction/session.

Batch job performance​

1. Process in chunks.
   • Do not process all 50k records in one transaction.
   • Use batches such as 500, 1,000, or 2,000 records depending on posting complexity.
   • Commit only at safe business boundaries, for example after each invoice or after a validated batch.
2. Use selective reads.
   • Use SetRange, SetFilter, SetCurrentKey, and FindSet.
   • Avoid broad FindSet() loops without good filters.
   • Use partial records with SetLoadFields() when you only need a few fields.

Example:

SalesHeader.Reset();
SalesHeader.SetCurrentKey("Document Type", "Posting Date");
SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Invoice);
SalesHeader.SetRange("Posting Date", WorkDate());
SalesHeader.SetLoadFields("Document Type", "No.", "Sell-to Customer No.", "Posting Date");

if SalesHeader.FindSet(false) then
    repeat
        // Lightweight validation only
    until SalesHeader.Next() = 0;

1. Avoid repeated expensive calculations in loops.
   • Cache setup records before the loop.
   • Cache lookup values in a Dictionary.
   • Avoid repeatedly reading the same Customer, Item, or setup records.
2. Use set-based operations where safe.
   • Prefer ModifyAll, DeleteAll, CalcSums, and queries where appropriate.
   • Be careful: table event subscribers on hot tables can force bulk operations into row-by-row operations.

Locking and deadlock reduction​

1. Keep transactions short.
   • Read setup and reference data before starting write-heavy logic.
   • Do not call external services while holding database locks.
   • Avoid long validation logic after records have been locked.
2. Lock late, not early.
   • Use LockTable() only when necessary.
   • Call it as late as possible.
   • Important: LockTable() affects subsequent reads for that table in the transaction, so using it too early increases lock duration.
3. Use a consistent update order.
   • If two processes update Sales Header, Sales Line, Item Ledger Entry, and a custom table, make sure they update those tables in the same order.
   • Inconsistent ordering is a common deadlock cause.
4. Index for the filters you update.
   • If your batch job filters by Status, Posting Date, Document Type, or a custom queue status, add keys that match those access patterns.
   • Do not add too many keys, because every insert/update must maintain them.
5. Avoid parallel posting against the same parent document.
   • Do not let multiple sessions post or insert child records for the same invoice/order/customer batch at the same time.
   • If external systems send child records for the same parent, serialize those calls or use OData $batch.
6. Use the Database Locks page and telemetry.
   • In Business Central online, enable Application Insights telemetry.
   • Review long-running AL operations, long-running SQL queries, lock timeouts, and web service request telemetry.

Data consistency with temporary tables and background sessions​

1. Do not rely on temporary tables across sessions.
   • Temporary tables are session-scoped.
   • A background session will not safely share the caller’s in-memory temporary table state.
2. Use persistent staging tables for cross-session work.
   • Store the work item in a real table.
   • Include fields such as Status, Attempt Count, Last Error, Correlation ID, Started At, and Completed At.
3. Make background processing idempotent.
   • A job may retry after failure.
   • The code must be safe to run again without duplicating posted documents or external API calls.
4. Use status transitions carefully.
   • Move records from Pending to In Progress.
   • Commit that status change.
   • Process the record.
   • Mark it Completed or Failed.

Example pattern:

IntegrationQueue.SetRange(Status, IntegrationQueue.Status::Pending);

if IntegrationQueue.FindSet(true) then
    repeat
        IntegrationQueue.Status := IntegrationQueue.Status::"In Progress";
        IntegrationQueue."Started At" := CurrentDateTime();
        IntegrationQueue.Modify(true);
        Commit();

        if ProcessQueueEntry(IntegrationQueue) then begin
            IntegrationQueue.Status := IntegrationQueue.Status::Completed;
            IntegrationQueue."Completed At" := CurrentDateTime();
        end else begin
            IntegrationQueue.Status := IntegrationQueue.Status::Failed;
            IntegrationQueue."Attempt Count" += 1;
        end;

        IntegrationQueue.Modify(true);
        Commit();
    until IntegrationQueue.Next() = 0;

Warning: only use Commit() where the business process can safely tolerate partial completion. Avoid adding commits inside Microsoft posting routines unless you fully understand the posting transaction boundary.

Event subscribers vs direct modifications​

1. Use event subscribers for Business Central cloud.
   • Direct base application modifications are not the right model for SaaS.
   • Events keep your extension upgradeable.
2. Keep event subscribers lightweight.
   • Avoid heavy reads, writes, external API calls, and broad loops inside posting events.
   • Use the subscriber to enqueue work, then let a background job process it.
3. Avoid too many subscribers on hot events.
   • Posting events fire frequently.
   • Multiple subscribers can multiply execution time.
4. Be careful with table trigger events.
   • Subscribers on OnBeforeModify, OnAfterModify, OnBeforeInsert, and similar table events can hurt performance on high-volume tables.
   • They can also interfere with bulk SQL optimizations.
5. Prefer purpose-specific integration events.
   • Subscribe close to the business event you need.
   • Avoid generic table events unless there is no better publisher.

SetAutoCalcFields vs CalcFields​

Use SetAutoCalcFields when you need the same FlowField value for many records in a loop. Use CalcFields when you only need the FlowField for a small number of records or conditionally.
Better for loops:

Customer.SetAutoCalcFields(Balance);
Customer.SetRange(Blocked, Customer.Blocked::" ");

if Customer.FindSet(false) then
    repeat
        if Customer.Balance > CreditLimit then
            DoSomething(Customer);
    until Customer.Next() = 0;

Less ideal in large loops:

if Customer.FindSet(false) then
    repeat
        Customer.CalcFields(Balance);

        if Customer.Balance > CreditLimit then
            DoSomething(Customer);
    until Customer.Next() = 0;

Also check whether the FlowField has suitable SIFT/index support. A badly supported FlowField can become a major bottleneck even if SetAutoCalcFields is used.

Practical checklist​

1. Enable Application Insights telemetry for the environment.
2. Profile the batch job with the AL Profiler or telemetry.
3. Identify the slowest event subscribers in the posting path.
4. Remove external API calls from posting transactions.
5. Move heavy custom validations to a pre-posting validation stage.
6. Add appropriate keys for queue, staging, and high-volume filter patterns.
7. Use SetLoadFields() for large read loops.
8. Use SetAutoCalcFields() for FlowFields needed on every loop iteration.
9. Chunk batch processing and commit only at safe business boundaries.
10. Use persistent staging tables instead of temporary tables for background sessions.

My suggested design for your case​

For your volume, I would use this pattern:

1. Import or identify invoices into a custom posting queue.
2. Run custom validations in a pre-validation job queue.
3. Mark valid records as Ready to Post.
4. Run posting in controlled chunks, grouped to avoid multiple sessions posting related records.
5. Write an outbox/integration queue after successful posting.
6. Let a separate job queue call external APIs with retry and backoff.
7. Use telemetry to tune the slowest table reads, FlowFields, event subscribers, and API calls.

That separation usually gives the biggest improvement because it reduces lock duration and removes unpredictable external dependencies from the posting transaction.