NAV 3.70 Item Re-numbering

KayewainKayewain Member Posts: 6
Does anyone have a solution for re-numbering a large number of items, in excess of 7000. Currently via the manual process its taking approx 8 minutes per item and blocks all users. Therefore not enough downtime to run as a job out of hours.

Regards
Kayewain

Comments

  • nunomaianunomaia Member Posts: 1,153
    It takes times to rename. Navision must go to all tables with relationships and also rename those tables.

    if you can't run a batch job after work hours, I can’t see other way around.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • themavethemave Member Posts: 1,058
    We had our solution center program a report for us, to do it.

    our particular need was to change a string within the part number to a new string. Our part numbers all follow a specific format, there is a series of letters, a dash and a series of numbers or letters. The first section represents a manufacture. and in our case that is the section we need to change some times. We enter the filter in the report request form and the report runs through the items renumbering them, there is an option to commit after each rename. It doesn't speed up the renumbering time, but it allows us to start it at night and renumber as many as posible during off hours. unfortunately it only does about 150 a night, so your 7000 would take a long time to complete.

    the report has two data items, one is the item table, the second is the integer

    here is the code on the item table section

    IF GETFILTERS = '' THEN
    ERROR('Please filter your selection of Items to be renumbered.');

    IF SectionDivider = '' THEN
    ERROR('Please specify a Section Divider.');

    IF (SectionNo < 1) OR (SectionNo > 9) THEN
    ERROR('Please enter a Section No. between 1 and 9.');

    IF ReplaceWithStr = '' THEN
    ERROR('Please enter a Replace with String value.');

    Item - OnAfterGetRecord()
    TempItem.INIT;
    TempItem."No." := "No.";
    TempItem.INSERT;

    Here is the code on the integer section


    SETRANGE(Number, 1, TempItem.COUNT);

    Window.OPEN(
    'Renumbering items...\\' +
    '#1######################################');

    Integer - OnAfterGetRecord()
    IF Number = 1 THEN
    TempItem.FIND('-')
    ELSE
    TempItem.NEXT;

    CLEAR(Section);
    CurrSection := 1;
    i := 0;
    WHILE (i <= STRLEN(TempItem."No.")) AND (CurrSection <= 9) DO BEGIN
    i += 1;
    IF COPYSTR(TempItem."No.", i, 1) = SectionDivider THEN
    CurrSection += 1
    ELSE
    Section[CurrSection] := Section[CurrSection] + COPYSTR(TempItem."No.", i, 1);
    END;

    NewItemNo := '';

    IF Section[SectionNo] <> '' THEN
    FOR i := 1 TO 9 DO BEGIN
    IF i = SectionNo THEN
    Section := ReplaceWithStr;
    IF Section <> '' THEN
    IF i = 1 THEN
    NewItemNo := Section
    ELSE
    NewItemNo := NewItemNo + SectionDivider + Section;
    END;

    IF NewItemNo <> '' THEN BEGIN
    Window.UPDATE(1, STRSUBSTNO('%1 -> %2', TempItem."No.", NewItemNo));
    _item.GET(TempItem."No.");
    IF _item.RENAME(NewItemNo) THEN BEGIN
    RenumCount += 1;
    IF CommitEachItem THEN
    COMMIT;
    END;
    END;

    Integer - OnPostDataItem()
    Window.CLOSE;
    COMMIT;
    MESSAGE('%1 out of %2 items renumbered', RenumCount, TempItem.COUNT);


    the request form has space to enter the divider we use in our part number, the section of the part number we want to replace, and the code we want to be replaced with
  • nunomaianunomaia Member Posts: 1,153
    themave:
    Therefore not enough downtime to run as a job out of hours

    I think Kayewain is asking a way of improve item rename performance. In huge databases it's takes time and there isn't a way to improve performance.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • themavethemave Member Posts: 1,058
    nunomaia wrote:
    themave:
    Therefore not enough downtime to run as a job out of hours

    I think Kayewain is asking a way of improve item rename performance. In huge databases it's takes time and there isn't a way to improve performance.
    I thought one reason there wasn't enough down time is he couldn't complete the whole batch in one night or weekend, this would allow him to do it in phases. but that may not help him.

    The only way I got it to speed up any, was to change a few primary keys, run the rename and then change the keys back. I don't have access to code units so I couldn't change the rename code to pick the best key.

    When you run the rename process on a single item, watch as it processes each item, it goes through a list and says searching sales invoice header table, ect, through all the tables, there will be a few that takes really long, on those tables you need to change the primary key start with the item number. this speeds up the process a lot. But you can't leave it that way because the rest of your regular activity will be slowed way down.

    Now if you are a developer maybe you can change the code to select the best key.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Hi,

    Try this:

    1. Export all tables to fob.
    2. Make a new database, restore backup to it
    3. In all tables disable all keys except primary
    4. Export modified tables to fob, then import it to prodution database
    5. Run rename process
    6. Import fob with original tables.

    Disabling keys will speed up rename, as system does not have to rebuild indexes and SIFT information every renamed item. Instead it will rebuild SIFTs in step 6.

    Disabling keys *may* slow down the process, so it migth be better to leave some keys turned on. You will have to do some tests - turn off all non primary keys and rename one item - when you see that system start to counting records in some table - that means you turned off some necessary key on that table.

    Instead of turning off keys you may clear SIFT fields (or tur all SIFT levels maintenance) in every key that has SIFT defined.

    Another method is to unload all necessary data to external file, rename them using SED for example, and reload the data.

    Regards
    Slawek.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • nunomaianunomaia Member Posts: 1,153
    Hi,

    Try this:

    1. Export all tables to fob.
    2. Make a new database, restore backup to it
    3. In all tables disable all keys except primary
    4. Export modified tables to fob, then import it to prodution database
    5. Run rename process
    6. Import fob with original tables.

    Disabling keys will speed up rename, as system does not have to rebuild indexes and SIFT information every renamed item. Instead it will rebuild SIFTs in step 6.

    Disabling keys *may* slow down the process, so it migth be better to leave some keys turned on. You will have to do some tests - turn off all non primary keys and rename one item - when you see that system start to counting records in some table - that means you turned off some necessary key on that table.

    Instead of turning off keys you may clear SIFT fields (or tur all SIFT levels maintenance) in every key that has SIFT defined.

    Another method is to unload all necessary data to external file, rename them using SED for example, and reload the data.

    Regards
    Slawek.

    if it takes 8 minutes to rename a item, probably it's a large database. Disabling keys the in a system that don't have no stop hours can create a serious performance problem.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • davmac1davmac1 Member Posts: 1,283
    Are you running the item renumbering client/server or as a single client on the server with the Navision database and a huge database cache?
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Hi,

    As I understood there are stop hours but the whole process (7000x8min) will take too long to fit that time.

    Disabling keys will not cause performance problems - it will kill Navision - all funcionality based on SIFT or non primary keys will fail. Every single SETCURRENTKEY will fail. Every form displaing anything based on SIFT field won't open. Every form using different key than primary.

    Regardless of that in my opinion renaming using described method is possible - but requires some tests. Of course it is possible to perform only in stop hours. All table object have to be prepared in a copy, tests should be run in a copy, and if everything went fine modified tables can be imported to production server, and have to be restored to the original state after rename, before system go online again

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.