No. Series – are they usable?

In Navision number series are used to handle invoices, credit memo’s, customers etc. – actually they are pretty widely used around in Navision.

So how does number series work?

Number Series is defined in one table 309 “No. Series Line” (actual in 2 tables – a header and a line table, but we are only interested in the line table). Here you must define a starting no. – which is of the type code. Every time, a record is being inserted into at table that uses no. series – a lock will be made on the line table; the next number will be calculated and returned and finally the lock will be released again.

All this is very fine – but what if 100 users were doing an insert on tables, that all uses no. series, at once. They would be locking for each other – which again means, that there would be some wait time.

Is this satisfying for a company, that lets say 100 users are waiting each for approx. 10 sec each in average. (The first user would wait 1 sec, then next 1,1 sec and so on). This is not satisfying for any companies.

So what can we do?

Remove every table that has a lot of real time transactions away from the no. series.

This can be done in different ways. One way is to use an Entry no., where autoincrement is activated, as key. Here you must remember to use biginteger, as integer is likely to run full earlier than biginteger. A second way would be moving no. series for specific areas (no. of tables) out of the no. series, to different setup tables.

Example: add a new no. series code field to a setup table; then add a function GetNextNumber, which increment the no. series field and returns that value. And finally add the call of GetNextNumber to the affected table (OnInsert).

Here you still have the risk for locks – but in this way you are only locking for one table / functionality and not as by using the no. series line table, for everything that uses no. series.

So back to my question – is no. series usable?

Yes – with tables, that is not having a lot of real time transactions.

No – with tables, that is having a lot of real time transactions. Here I would recommend using biginteger – and if the key field still should be code, then move the no. series functionality to a different table.