autoincrement in Value Entry table

rixrix

Hello

I would like to ask you ......
Now we have a lot of blockings causet of command:
SELECT TOP 1 * FROM "VEREX"."dbo"."VEREX-ELTO$Value Entry" WITH (UPDLOCK, ROWLOCK) ORDER BY "Entry No_" DESC

Can we change the process of inserting into this table in the way we will make "Entry No_" as autoincrement so "Select top 1 ..." will not be needed for inserting records?

Could be there some problems with rollback? (which will cause "holes" in the numbers row)

Thank you

matteo_montanari

rixrix wrote:

Hello

I would like to ask you ......
Now we have a lot of blockings causet of command:
SELECT TOP 1 * FROM "VEREX"."dbo"."VEREX-ELTO$Value Entry" WITH (UPDLOCK, ROWLOCK) ORDER BY "Entry No_" DESC

Can we change the process of inserting into this table in the way we will make "Entry No_" as autoincrement so "Select top 1 ..." will not be needed for inserting records?

Could be there some problems with rollback? (which will cause "holes" in the numbers row)

Thank you

Hi

Don't do this change. Nav Db structure/programs don't support a change like this. It will be a disaster.

Matteo

Alex_Chow

No, never change how standard NAV works with SQL. You'll create more problems than solutions.

How is the select statement you listed related to the insert?

rixrix

Hi
the select gets highest "Entry No_" , locks the table and in the following insert uses "Entry no_" + 1
It is done very often.. As I said we have 200 users, 12 branches
And on each are done same things which uses this table

The idea was to use autoincrement for that field, so no select would be needed.

matttrax

What else have you done to solve the problem? There are a TON of posts on SQL optimization on the forums. Rewriting the way NAV and SQL talk is not a good plan, if it's even possible.

200 users is a lot, but how many reads / writes to the Value Entry table? You can put the table on a dedicated disk or add more disks to speed up performance. Lots of other options have been talked about in other posts.

rixrix

I think we tried almost everything in sql server optimalization

Db is on HP disk array on 2 physical disks in 8 files. Nothing else is on that disks. Also 32GB ram should be enough
We tried index optimalization also with cooperation with mr Stryk (www.stryk.info) ...(we haven't finished yet)

We also changed workflow in the company, did some night processing where we could.
But it is still not enough.

These "Entry tables" are heavily used and we can't solve our problems another way (at least we do not know how) because the Navision "core" uses them.

So we were thinking about this partial change which should help, if it works....
There are other similar tables like ILE,.... which works the same way

But I am just asking, becouse of course we are not sure if smthng will not go bad.... and we will not even try as I look at your answers :-)

matttrax

Wow...that's way more than I was expecting. Sounds like you are taking all of the right steps.

One thing you could consider is splitting the single database into multiple databases on different servers / drives. You would of course need to get properly licensed for such a thing. Essentially splitting a 200 user database into 2 - 100 user databases, or 4 - 50 user databases (theoretically). I don't know how many of your users access multiple companies.

Just a thought, though, as I have never had a company have bad enough locking to really warrant digging into it that much.

rixrix

Hi I do not think splitting db on more servers will help, because every company has one ILE, VE , .. table and if it's locked it has to be locked on all servers.
And I do not think that speed, power of our HW is the problem
We discussed HW requirements directly with microsoft and everything is over requirements they said.

Maybe Oracle DB would be more powerful, but it is not possible to use it so we must do something with MSSQL ;-)
Or with the way how client generate sql queries.... dunno

o course if all db was in the RAM .... maybe something would be faster ... but our DB has now approx 250 GB so this way is also not possible :-)

We are an wholesale company so "posting" is our main activity and almost everything is doing this all day long.

P.S. we were also optimizing raid STRIPE size, partition offsets, NTFS allocation unit sizes ....

rmv_RU

At one of the projects I have shared the Entry No. by the locations.
This is sample implementation:
1. Switch Entry No. field on 399 table to Autoincrement.
2. Add "Entry Range" field (text50) to the Location table.
3. Add new functions to posting codeunit (22):

ItemLedgGetNextEntryNo(lLocationCode) : Integer
Location.get(lLocationCode);
Location.testfield("Entry Range");
ILE.locktable;
ILE.setfilter(:"Entry No.", Location."Entry Range");
if ILE.Find('+') then
  exit(ILE."Entry No.")
else
  exit(ILE.getrangemin("Entry No.");


ValueEntryGetNextEntryNo(lLocationCode) : Integer
..........................................

4. Replace

ItemLedgerEntry.Locktable;
if ItemLedgerEntry.find('+') then
  ItemLedgerEntryNo:=ItemLedgerEntry."Entry No.";

ValueEntry.Locktable;
if ValueEntry.find('+') then
  ValueEntryNo:=ValueEntry."Entry No.";

with

ItemLedgerEntryNo:=ItemLedgGetNextEntryNo("Location Code");
......
ValueEntryNo:=ValueEntrytNextEntryNo("Location Code");

5. Enjoy ... and look for bugs .

rhpnt

rixrix wrote:

Hello

I would like to ask you ......
Now we have a lot of blockings causet of command:
SELECT TOP 1 * FROM "VEREX"."dbo"."VEREX-ELTO$Value Entry" WITH (UPDLOCK, ROWLOCK) ORDER BY "Entry No_" DESC

Can we change the process of inserting into this table in the way we will make "Entry No_" as autoincrement so "Select top 1 ..." will not be needed for inserting records?

Could be there some problems with rollback? (which will cause "holes" in the numbers row)

Thank you

There's something strange about what you are stating. I have never heard that any Entry table would cause locks!? Currently I work for a company with nearly 500 concurrent users located in 150 branch offices and I never experienced anything like this. Although the DB is heavily modified we experience no locks at any time. If you look closely at what you pasted you'll see that SQL locks only the row in use not the whole table. Your locking problem must have another source. Check your NAV objects again for any strange modification or even better use the NDT and search for LOCKTABLE.

rixrix

That command is logged as "Blocking CMD" on table ILE, VE, ...

... I suppose all other "insert actions" want to use that row and can't , maybe they are then blocking other and other people somehow ... There is always one who starts that blockings

rixrix

rmv_RU: Thank you very much. Will have to look at it and will let you know if it was usable

rhpnt

rixrix wrote:

That command is logged as "Blocking CMD" on table ILE, VE, ...

... I suppose all other "insert actions" want to use that row and can't , maybe they are then blocking other and other people somehow ... There is always one who starts that blockings

You suppose wrong. SQL server has its own queue which organizes transactions and this system hangs only if deadlocks occur. Deadlocks are always raised by clients explicitly locking whole tables by not commiting transactions.

Since you didn't provide the exact error/lock message I can only guess, but again, check your NAV code.

p.s.-Indexes have nothing to do with write transactions, so in your case optimizing them is a waste of time.

bbrown

Db is on HP disk array on 2 physical disks in 8 files...

...but our DB has now approx 250 GB...

A 250 GB database on only 2 drives and MS says your hardware is over spec? Really?

rixrix

Sorry , I didn't said it properly

It is on two logical drives in 4 files (2+2)
Of course physically it is on more drives ... RAID 1+0

logs + backups are on different drives

rixrix

Production Server
HP ProLiant DL380G5 E5335 2G Base 2U Rack
2 x Intel Xeon E5335 Quad Core Processor 2GHz, 8MB (2 x 4 MB) L2 cache, 1333 MHz FSB
7 processors for SQL
32 GB RAM
26 – 28 GB for SQL

Clients
2GB of RAM
32-bit

Storage System
4 different LUNs
E: 4 + 4 RAID 10 – for SQL data-files
H: 4 + 4 RAID 10 – for SQL data-files
F: 4 + 4 RAID 10 – for SQL transaction log
G: 1 + 1 RAID 1 – for tempdb

Network
100 terminal users are connecting via 2 Citrix Servers
(14 GB RAM, 2 x Intel Xeon E5335 Quad Core Processor 2GHz, 8MB (2 x 4 MB) L2 cache, 1333 MHz FSB0).
The rest of the users (+- 30) are using a 1 GB switch locally.