Few years ago, during one technical session of Directions, someone said: “Job Queues are old”, “We discourage to use them intensively”, “Use external scheduler or subscriptions to trigger events, avoid polling”.
Yeah, but in a high concurrency environment, Job Queues are irreplaceable, and I do not know a better method to serialize operations.
Concurrency Improvements
Last Business Central versions unleashed using of sequences in many codeunits of posting. For example, the “Item Jnl.-Post Line”. Skip of the old LOCKTABLE / FINDLAST:
if (ItemLedgEntryNo = 0) and InvtSetup.UseLegacyPosting() then begin
GlobalItemLedgEntry.LockTable();
ItemLedgEntryNo := GlobalItemLedgEntry.GetLastEntryNo();
GlobalItemLedgEntry."Entry No." := ItemLedgEntryNo;
end;Use of the new sequence:
[InherentPermissions(PermissionObjectType::TableData, Database::"Item Ledger Entry", 'r')]
procedure GetNextEntryNo(): Integer
var
SequenceNoMgt: Codeunit "Sequence No. Mgt.";
begin
exit(SequenceNoMgt.GetNextSeqNo(DATABASE::"Item Ledger Entry"));
end;Taking the next Entry No. was one of the most performance issues of NAV/BC in the past.
OK, has everything been resolved with sequences? 😅
SIFT Buckets
As you know, all SUMs in Business Central are evaluated in real-time by SQL Server. There are no calculated tables with balancies, inventories and so on. Commercially they call this “SIFT” (Â SumIndexField Technology) but it is a simple “Indexed View” that anyone can use also in own database:
https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views
An “Indexed View” is a view, for example with a SUM, with a Clustered Index on-top. SQL Engine maintains automatically the result of the SUM at every Insert, Update or Delete of the binded table. So, the value of the SUM can be retrieved without seeking. Other databases has a similar feature (for example “Materialized View”) but “Indexed Views” has the advantage of automatic in-transaction refresh.
“In-transaction refresh”? 🥶
But what’s happen if two users post concurrently on Item Ledger Entry for the same Item? LOCK HAPPENS! So sequences are unuseful?
Partially: Microsoft added a new field in many SIFT called “SIFT Bucket No.” so, for the previous example, lock happens only for the same Item and Bucket. How many buckets are available? Five.
trigger OnInsert()
begin
Rec."SIFT Bucket No." := Rec."Item Register No." mod 5;
end;Simply the Base Application takes the Register Number and calculate the modulus with 5, obtaining a kind of “round robin” SIFT 0 -1 -2 -3 -4 – 0 – 1 – 2 – 3 … The result is well visible querying SIFT via SQL (“NOEXPAND” hint instructs SQL to return pre-calculated SUM).
As you can see, the same Item is repeated for any Bucket.
Is five concurrent posting enough? It depends.
If you cannot go parallel, go serial
It’s now that Job Queues make the difference.
Start with an example. How many cores has your notebook? Eight. How many parallel task can execute concurrently? Eight. So, there is any benefit launching more than eight threads at once? No! Moreover, more threads than cores, increases “scheduler” overload, getting worse performance.
Don’t check the concurrency it only creates problems, so you cannot trust only in sequences or buckets.
Job Queues allows you to serialize tasks (in background) eliminating any concurrency. The user experience? Great! User press a button and gets “Your request has been enqueued”. After few seconds, the request is processed, serving users in a FIFO way.
To avoid too many records in Job Queue Entry, in our APPs we use “Batch Activities”, a table that enqueues all the requests.
So, only few Job Queue processes run, with the concurrency that you want.
Furthermore, these processes never go in error, processing each request atomically.
Conclusion
Yes, it’s possibile to run a huge (very huge!) warehouse without any concurrency issue. And the solution has always been available! 😎