X++: ttsBegin - ttsCommit placement and performance problem

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

Could someone please explain why is the second update block 20 times faster then the first block?
The only difference is the placement of ttsBegin and ttsCommit.
I have read that best practice is to have transactions as small as possible.
What exactly is causing the big difference in speed?

This is slower:

while select forUpdate someTable where someTable.Field1 == “12345”
{
ttsBegin;
calcTrans.Field2 = “testing”;
calcTrans.doUpdate();
ttsCommit;
}

This is much faster:

ttsBegin;
while select forUpdate someTable where someTable.Field1 == “12345”
{
calcTrans.Field2 = “testing”;
calcTrans.doUpdate();
}
ttsCommit;

Thanks!

2

Hi,

I think this is because you call x database accesses (cause you’re inside a while loop) instead of only one in the second case.

Best regards

3

Could you please elaborate on that?
What do you mean by “call x database accesses”?

Doesn’t the second case fetch record by record?

4

Each case fetch record by record, but in the first case, for each loop, a connection to database is open and closed (ttsbegin and ttscommit), in the second case, a single connection is used, you call ttsbegin and ttscommit only once, so it’s faster

5

Could someone confirm Thomas’s opinion?

6

Yes, you are opening and closing your transactions every time in the first case. So that means when you update 20 rows, you will have 20 transactions instead of 1.