Options

autoincrement in Value Entry table

rixrixrixrix Member Posts: 121
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

Comments

  • Options
    matteo_montanarimatteo_montanari Member Posts: 189
    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
    Reno Sistemi Navision Developer
  • Options
    Alex_ChowAlex_Chow Member Posts: 5,063
    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?
  • Options
    rixrixrixrix Member Posts: 121
    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.
  • Options
    matttraxmatttrax Member Posts: 2,309
    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.
  • Options
    rixrixrixrix Member Posts: 121
    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 :-)
  • Options
    matttraxmatttrax Member Posts: 2,309
    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.
  • Options
    rixrixrixrix Member Posts: 121
    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 ....
  • Options
    rmv_RUrmv_RU Member Posts: 119
    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 :).
    Looking for part-time work.
    Nav, T-SQL.
  • Options
    rhpntrhpnt Member Posts: 688
    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.
  • Options
    rixrixrixrix Member Posts: 121
    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
  • Options
    rixrixrixrix Member Posts: 121
    rmv_RU: Thank you very much. Will have to look at it and will let you know if it was usable
  • Options
    rhpntrhpnt Member Posts: 688
    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.
  • Options
    bbrownbbrown Member Posts: 3,268
    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?
    There are no bugs - only undocumented features.
  • Options
    rixrixrixrix Member Posts: 121
    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
  • Options
    rixrixrixrix Member Posts: 121
    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.
Sign In or Register to comment.