Autoincrement in ledger tables

davmac1 · 2008-05-26

I have reviewed some of the previous posts about autoincrement. Also asked a Microsoft employee about it. His comment is the current locktable method will stay as long as there is a native database.
Jörg A. Stryk wrote that he had implemented autoincrement, but added GUID as the clustered index to avoid locking problems.
One question I have about this method, don't you end up with frequent page splits if you use this method, which you will never have with a clustered index based on entry no.? Do you start out with a low fill factor on the ledger tables to reduce the occurence?
Has anyome been running at least several months on a high transaction database using autoincrement and is stilkl happy with it?

stryk · 2008-05-26

Hi!

Well, with the appropriate Fillfactor you won't get that many Page Splits, but probably more than with the "Entry No." CI. Of course, a GUID (16 bytes) index is remarkably larger than an integer (4 bytes) index, and the FF needs to be lower than 100% - hence, a GUID index is larger and forcing "physical disorder". The performance when Reading may be worse, too. The GUID thing is inteded to prevent blocking issues only.

That's what I tried to point out during my presentation (and in my BLOG): It always depends! It's all a matter of balance! There is no "silver bullet" to fix things, every solution has advantages and disadvantages ... that's what makes troublshooting such a "tricky business" ...

You really have to test this first. And have in mind: DON'T DO IT ON FISCALLY RELEVANT TABLES like G/L Entry, VAT Entry, Value Entry, Customer Ledger Entry, etc.!

kriki · 2008-05-26

You can use the autoincrement-property without problems. When 2 sessions are inserting records in the table with a clustered key that is autoincrement, they DON'T lock each other.
I just tried it to be sure.
I created a table with 2 fields in it : the clustered index (an integer) that is autoincrementing and a session ID in which I save the sessionID of the session.
I launched 2 clients to insert 10000 records in it in 1 transaction. Then I checked the records and I saw that the session-ID's alternate between the 2 sessions!

The GUID is another way to avoid the locking, but has some extra problems:
-it is BIG in confront with an integer (or even a biginteger).
-it can create a lot of pagesplits in the database, depending on the fillfactor on the index on it.

kine · 2008-05-26

kriki wrote:

You can use the autoincrement-property without problems. When 2 sessions are inserting records in the table with a clustered key that is autoincrement, they DON'T lock each other.
I just tried it to be sure.
I created a table with 2 fields in it : the clustered index (an integer) that is autoincrementing and a session ID in which I save the sessionID of the session.
I launched 2 clients to insert 10000 records in it in 1 transaction. Then I checked the records and I saw that the session-ID's alternate between the 2 sessions!

The GUID is another way to avoid the locking, but has some extra problems:
-it is BIG in confront with an integer (or even a biginteger).
-it can create a lot of pagesplits in the database, depending on the fillfactor on the index on it.

Yes, but still you need to have on mind, that when posting, you need to lock the table to prevent others to change the data during your posting, else you can have inconsistent database (e.g. when used with Item Ledger Entry).

davmac1 · 2008-05-26

Since NAV is inserting ledger rows, why would there be data inconsistency? Especially if all ledger tables were changed to use identity columns as the clustered index?

kine · 2008-05-26

davmac1 wrote:

Since NAV is inserting ledger rows, why would there be data inconsistency? Especially if all ledger tables were changed to use identity columns as the clustered index?

Because if you read some data to e.g. calc sum, and you create new entry based on this sum, and some other process will read same set of record, calc the sum and create another record, you will have two records based on same data but together giving you wrong result... ;-) it is why LockTable is used for - to prevent changes of record set you have read....

davmac1 · 2008-05-26

Typically the ledger table is locked for inserting new rows. Are the tables whose rows are summed locked as well or just the rows - like the sales header?

kine · 2008-05-26

If you use LockTable, all records you read from that table will be locked...

davmac1 · 2008-05-26

The codeunit for posting GL Ledger Entries inserts new rows - if the table was not locked, how would that affect the correctness of new rows?
As long as the commit is done after all the ledger rows have been inserted, wouldn't that keep the entries correct and consistent?

bbrown · 2008-05-26

The posting routines are also dependent on the entries for a given posting to be consecutive.

Autoincrement in ledger tables

Comments

Categories