Business central blog
Subscribe to blog and get news about new posts.

Performance of Number Series and Number Sequence

It is fairly common practice to use Number Series to generate a key field value for a table in Business Central. This is very convenient, but do you know how slow it is? Let's try to look at examples and performance measurements, talk about Number Series and Allow Gaps, as well as Number Sequence.
Part 2

Number Series

Number Series is the most common way to generate numbers in Business Central for tables. For example, this method is used for Sales/Purchase documents and journal lines. In terms of performance we are interested in the "Allow Gaps in Nos." setting, this setting determines whether gaps in generation are possible.
In order to test performance I created a Number Series Performance Test page, for the current test I will run 100,000 iterations. The test code itself is quite simple, here is an example of the results and code:

local procedure TestNoSeriesNoGaps()
var
	NoSeriesManagement: Codeunit NoSeriesManagement;
	ListOfText: List of [Text];
	StartDateTime: DateTime;
	i: Integer;
begin
	StartDateTime := CurrentDateTime();
	for i := 1 to NumberOfIterations do
		ListOfText.Add(NoSeriesManagement.GetNextNo(NoSeriesCodeNoGapsLbl, Today(), true));
	Message('Number series with no gaps: %1 iterations took %2.', NumberOfIterations, CurrentDateTime() - StartDateTime);
end;

local procedure TestNoSeriesGaps()
var
	NoSeriesManagement: Codeunit NoSeriesManagement;
	ListOfText: List of [Text];
	StartDateTime: DateTime;
	i: Integer;
begin
	StartDateTime := CurrentDateTime();
	for i := 1 to NumberOfIterations do
		ListOfText.Add(NoSeriesManagement.GetNextNo(NoSeriesCodeGapsLbl, Today(), true));
	Message('Number series with gaps: %1 iterations took %2.', NumberOfIterations, CurrentDateTime() - StartDateTime);
end;
As a result the method with gaps is more than twice as fast as the method that excludes gaps, what is the reason? The reason is that in order to ensure that the correct number is issued under race conditions, we have to make locks at the table record level. This in turn will make sure that the next number will be generated without gaps, but at the same time it will slow down the process because the process of locking is an expensive and slow operation from the point of view of SQL itself.
In addition it means that for heavy processes where many numbers need to be generated it is very dangerous to use Number Series where gaps are not allowed. There is a risk that two processes in different sessions will intersect and one of them will fail because the No Series Line table is locked by another process in a parallel session.
So, Number Series where gaps are not allowed is used Record.LockTable on the No Series Line table, and in case gaps are allowed Number Sequence is used.

Number Sequence

Number Sequence is a data type that allows you to create, modify and delete a sequence of numbers, as well as get a new number in the sequence. And getting a new number in the sequence does not block any operations.
Based on the fact that we know that Number Series where gaps are allowed uses Number Sequence, can we assume that the performance of such Number Series will be the same as Number Sequence? Let's check it out.

local procedure TestNumberSequence()
var
	ListOfText: List of [Text];
	StartDateTime: DateTime;
	i: Integer;
begin
	StartDateTime := CurrentDateTime();
	for i := 1 to NumberOfIterations do
		ListOfText.Add(Format(NumberSequence.Next(NumberSequenceLbl)));
	Message('Number sequence: %1 iterations took %2.', NumberOfIterations, CurrentDateTime() - StartDateTime);
end;
Unexpectedly pure Number Sequence is faster than Number Series, what is the reason? Well, you should remember that Number Sequence works with numbers and outputs just a number, while Number Series can work with text and output numbers with text, for example INV-00001. This means that additional time is spent on different checks and working with text.

Numeric Counter

But you know what would be really faster? A simple numeric counter will be the fastest solution.

local procedure TestNumberIncrement()
var
	ListOfText: List of [Text];
	StartDateTime: DateTime;
	i: Integer;
begin
	StartDateTime := CurrentDateTime();
	for i := 1 to NumberOfIterations do
		ListOfText.Add(Format(i));
	Message('Number increment: %1 iterations took %2.', NumberOfIterations, CurrentDateTime() - StartDateTime);
end;
Unfortunately the speed is so fast that you can't see on this scale that it took 10 ms. So we should always use Numeric Counter? No of course not, each of these methods serves its own purpose and should be applied accordingly.

Summary

So, we have found out that Number Series where gaps are not allowed is the slowest way of generation, besides it blocks the No. Series Line table at runtime.
Number Series where gaps are allowed uses non-blocking Number Sequence with additional checks and checks to support text output and is more than twice as fast.
Number Sequence directly will run even faster because only numeric output is supported and there are no additional checks.
Numeric Counter is the fastest way to generate numbers, but without any memory or history of previous generations.
So what should we use? There is no one-size-fits-all answer, we use what is most appropriate in the current situation.
Do you have a button on Customer that creates a Sales Order (such a button already exists btw)? Use Number Series where gaps not allowed from Record.Insert trigger. Since the button creates one Sales document and is not used too often, the performance will be sufficient, plus we will be sure that the document is assigned a consecutive number without gaps.
Do you have a temporary buffer table with Entry No. as Primary Key into which you populate thousands of records to count some data? Then use simple Numeric Counter, you don't need to know the previous issued number, it doesn't matter what will be in Entry No., the main thing is that it was a unique number, since a lot of data is generated it makes sense to choose the most efficient way.
Do you have two Job Queue that create hundreds of Purchase Quote based on two different APIs? In that case it is fine to use Number Series where gaps are allowed. This way we will be sure that one process will not block the other from getting the number and we will get a consistent number, maybe with gaps, but definitely unique with pretty good performance.
Remember each of these methods serves its own purpose and should be applied accordingly.

Test Environment

Docker Sandbox
US Business Central 23.2 (Platform 23.0.15021.0 + Application 23.2.14098.15042)

Microsoft Windows 10 Pro
Processor: 13th Gen Intel(R) Core(TM) i7-13700KF
Motherboard Name: Asus ROG Maximus Z790 Hero
RAM: x2 G Skill F5-6000J3238G32G 32 GB DDR5-4800 DDR5 SDRAM
Video Adapter: NVIDIA GeForce RTX 4090

Source Code

February 15, 2024
Author: Volodymyr Dvernytskyi