Autoincrement property

NavStudent

Hello

I have a table that has "line no." (integer) as primary Key.

A lot of people will write to this table simultaneously.

This table will become huge. 10-50 million records.

I can change the PK to User ID, “Line No.”, and find what the Line next “line No.” will be.
The Other option is to change the “Line No.” Property to auto-increment.
That way SQL will determine the next PK and I don’t have to worry about concurrency. Because my line will always be inserted.
So my question is performance. Which table structure will perform better in 10-50 million range.
In term of inserting, having integer as PK its advantages. But will auto-increment haven any performance issues on sql?

diptish.naskar

Go with the auto increment propert, this will make ur life easier.

nunomaia

I avoid using autoincrement because it doesn’t guaranties that numbers are fully sequential. You can have gaps between lines numbers, and when you try to use register tables you can’t relate begin and end posting.

You will only get a performance increase of using autoincrement, if you don’t lock tables during posting, but that will lead to having ledger entries lines of posts that are done in same time.

NavStudent

the sequencial property is of no issue to me.

I would like navision to change that as well.
It will allow a lot more users to connect and process transactions.

No Locking!!!!

In regards to sequencial numbering. Every user can have it's own sequencial No. that way you garantee sequencial number for that user.

The PK of the table can be auto incremented, and you have field that is
User ID, and "Sequence No."

BtW Customer Ledger , Vendor Ledger, they all miss numbering in their "Entry No.".
I haven't heard anybody complaining about it.

Marije_Brummel

NavStudent wrote:

, and you have field that is
User ID, and "Sequence No."

BtW Customer Ledger , Vendor Ledger, they all miss numbering in their "Entry No.".
I haven't heard anybody complaining about it.

these are connected to the g/l. Try to open a cust/vend entry from the registers.
Changing this is of no use since cust/vend entries are created during g.l posting which is a serialised transaction anyway.

NavStudent

I know that.

They are serializing the transactions for GL in order to maintain sequencial "entry No."

You can maintain sequancial numbering by other means, as I explained, which won't lock people out, and you don't need to have a serialized transaction.

DenSter

Take a look at a G/L Register. You will see a "From Entry No." field and a "To Entry No." field. Looking at a Cronus US database, register number 1 has numbers 1 through 33 as the entry numbers, and register 2 has 34 to 37.

The integrity of those numbers are maintained by the way that the posting routines are serialized. The concept of registers is that one block of entries belong to one transaction. If you hand over control of the sequential numbers to SQL Server, you detroy that concept. Say you have two people posting those two registers at the same time, then you can't guarantee that a block of entries belong to one register. Numbers 1 to 12 may belong to register 1, 13 to register 2, 14 to 17 to register 1, etcetera.

Just leave the standard posting routines alone. It's of course alright to add fields, or to insert a function call for some functionality, but you should just stay away from trying to change the way that core functionality works. There are other things you can do that will make a much bigger impact.

Auto-increment is NOT the right solution for eliminating locks and blocks in ledger posting.

NavStudent

I am not planning to change that.
I am suggesting that there is better way to do this then what standard navision has done.

The whole G/L Register concept needs to thought through.

In regards to G/L Registers, the whole "from entry No.", "to entry no" idea can be improved.
You can simply accomplish that by having a "Batch ID" field that way you can filter on the "batch ID".
"Batch ID" would be user specific ID + "incremented No.".

GLRegsiter PK would be "Batch ID".


In order for Navision to support more than 200 people effeciently, they have to do some redesign and think of ways to eliminate locking.


They are releasing a 3 tierd system for god's sake just so that they can have better scalability.

It's much cheaper to redesign some tables.

Granted autoincrement does not work on C/Side, but they don't have to worry about that in 5.1

ayashi

The problem with auto increment is that the sql server user must be a db_owner or sysadmin. I tried to use auto increment once, but when the user is not administrator just having public role, the insert process gave me error message, said that I don't have permission to use SET command.
Am I the only one who experience this problem ?

bbrown

ayashi wrote:

The problem with auto increment is that the sql server user must be a db_owner or sysadmin. I tried to use auto increment once, but when the user is not administrator just having public role, the insert process gave me error message, said that I don't have permission to use SET command.
Am I the only one who experience this problem ?

This problem occurs if you attempt to insert a non-zero value into an auto-increment field. Set to zero and let the system determine the next number.