🧠 Avoiding forUpdate and Full Table Scans in D365 FO: Lessons from Tier-5 Performance Testing

When stress-testing a Tier-5 Premier Performance environment in Dynamics 365 Finance & Operations (D365 FO), performance bottlenecks become painfully visible.

Recently, while running a work completion load script with 25 concurrent workers, we repeatedly hit 500 Internal Server Errors. After reviewing logs and SQL traces, the primary culprit was clear:

❌ forUpdate inside loops and full table scans on large transactional tables caused SQL blocking, deadlocks, and lock escalation under heavy load.

🔍 Why forUpdate Inside Loops Can Hurt Performance

When you do this:

while select forUpdate rec from WMSOrderTrans
{
    rec.WorkCompleted = true;
    rec.update();
}
        

Each iteration triggers:

• One SQL round-trip per record
• Immediate row-level locking → contention & deadlocks with parallel workers
• High transaction overhead → reduced throughput under load

In performance test environments, this can collapse under concurrency, resulting in timeouts & 500 errors.

✅ Initial Optimization Idea: Bulk Fetch, Then Update

A common optimization is:

List recIds = new List(Types::Int64);

// Step 1: Fetch all RecIds (no locks)
while select RecId from WMSOrderTrans where Status == Open
{
    recIds.addEnd(WMSOrderTrans.RecId);
}

// Step 2: Update in one transaction
ttsBegin;
for (int i = 1; i <= recIds.length(); i++)
{
    WMSOrderTrans rec = WMSOrderTrans::findRecId(recIds.get(i), true);
    rec.WorkCompleted = true;
    rec.update();
}
ttsCommit;
        

This reduces SQL round-trips, improves throughput, and avoids long-running locks.

⚠️ BUT… What About Concurrency?

A valid concern arises in multi-user environments:

Between Step 1 and Step 2, the record’s status could have changed, another process might have updated it, or it might now be locked.

In transactional scenarios (e.g., live systems), this introduces a stale update risk.

That’s why some recommend keeping ttsBegin and ttsCommit inside the while select, so each record is fetched, locked, and updated immediately ensuring data consistency, but at the cost of lower performance under bulk load.

✅ The Balanced Middle Ground

For scenarios requiring both performance & safety, add status re-validation before updating:

List recIds = new List(Types::Int64);

// Step 1: Bulk fetch candidate records
while select RecId from WMSOrderTrans where Status == Open
{
    recIds.addEnd(WMSOrderTrans.RecId);
}

// Step 2: Update only if still valid
ttsBegin;
for (int i = 1; i <= recIds.length(); i++)
{
    WMSOrderTrans rec = WMSOrderTrans::findRecId(recIds.get(i), true);

    // ✅ Double-check status to avoid stale updates
    if (rec.Status == Open)
    {
        rec.WorkCompleted = true;
        rec.update();
    }
}
ttsCommit;
        

✔ Better performance – fewer SQL calls

✔ Still safe – prevents overwriting newer changes

✔ Concurrency-friendly – avoids stale updates

📊 When to Use Which Approach

Batch jobs or Tier-5 Performance Tests (isolated environments)

• Use bulk fetch + update to maximize throughput.
• Suitable when no other users or processes are modifying those records simultaneously.

Real-time, multi-user transactional updates

• Use inline forUpdate with ttsBegin and ttsCommit inside the loop.
• Ensures strong data consistency, avoiding stale updates in high-concurrency scenarios.

Mixed or unpredictable environments

• Use bulk fetch with re-validation before update.
• Balances performance and safety, preventing overwriting newer changes while still reducing DB round-trips.

🔄 Visualizing the Difference

Here’s a quick flowchart to compare Safe Inline Updates vs Bulk Update with Re-validation:

💡 Key Takeaways

1. Full table scans & forUpdate inside loops kill performance under load.
2. Bulk fetch improves throughput but risks stale updates in concurrent environments.
3. Always balance performance vs consistency based on the scenario.
4. Re-validate before updating in mixed or unpredictable environments.

Performance isn’t just about hardware—it’s about writing concurrency-aware, scalable code.

Have you faced a similar challenge in your D365 FO projects? How do you balance performance vs consistency? Let’s discuss!

#D365FO #Dynamics365 #PerformanceTesting #Concurrency #Tier5 #SQLTuning #Xpp #MicrosoftDynamics