Performance of the Delete Invoiced Sales Order

integrated
integrated Member Posts: 22
edited 2008-11-26 in NAV/Navision Classic Client
I've been the Database Resource Kit (DRK) these days.
One of my clients are hitting some performance issues, and I became sensitive to all tunable portions of the NAV codes.
From the Delete Invoiced Sales Order object (# 299), I found the following implementation:
SalesOrderLine.RESET;
SalesOrderLine.SETRANGE("Document Type","Document Type");
SalesOrderLine.SETRANGE("Document No.","No.");
SalesOrderLine.SETFILTER("Quantity Invoiced",'<>0');
IF SalesOrderLine.FIND('-') THEN BEGIN
  SalesOrderLine.SETRANGE("Quantity Invoiced");
  SalesOrderLine.SETFILTER("Outstanding Quantity",'<>0');
  IF NOT SalesOrderLine.FIND('-') THEN BEGIN
    SalesOrderLine.SETRANGE("Outstanding Quantity");
    SalesOrderLine.SETFILTER("Qty. Shipped Not Invoiced",'<>0');
    IF NOT SalesOrderLine.FIND('-') THEN BEGIN
      SalesOrderLine.LOCKTABLE;
      IF NOT SalesOrderLine.FIND('-') THEN BEGIN
        SalesOrderLine.SETRANGE("Qty. Shipped Not Invoiced");
        DocDim.SETRANGE("Table ID",DATABASE::"Sales Line");
        IF SalesOrderLine.FIND('-') THEN
          REPEAT

From the DRK, each FIND('-') call will force a new cursor to be used, and for REPEAT-UNTIL loop FINDSET is superior to FIND('-'). So what would be the intention of nesting FIND('-') like the example above, instead of scrambling all SETRANGE filters together, and call FINDSET once and for all?

Comments

  • idiot
    idiot Member Posts: 651
    FINDSET, FINDFIRST, FINDLAST, ISEMPTY is only available from 4.01 onwards.
    Any FIND('-') is due to old codes which M$ does not find it profitable to update for new versions.
    NAV - Norton Anti Virus

    ERP Consultant (not just Navision) & Navision challenger
  • integrated
    integrated Member Posts: 22
    I understand that those functions are not available before NAV4.0.
    The script I posted is from NAV5.0 sp1.
    I wonder when do they find it profitable :shock:
    Such codes are indeed, everywhere...

    But what are about the nested FIND('-')?
    1. WHERE Doc#=Doc#, Type=Type, "Quantity Invoiced" <> 0
    2. Get result set
    3. Remove from WHERE clause "Quantity Invoiced" <> 0
    4. Add to WHERE clause: "Qty Shipped Not Invoiced" <> 0
    5. Get result set
    6. Remove from WHERE clauase "Qty Shipped Not Invoiced" <> 0
    7. ...

    Any particular reason to such filter-fetch-clear nesting you think?
    The sequence looks like a series of AND clause
  • kine
    kine Member Posts: 12,562
    You can translate it like:

    is there any line which have something invoiced?
    if yes, is there any line with some Outstanding quantity?
    if no, is there any line with some quantity shiped, but not invoiced?
    if no, lock the records and delete the order...

    You cannot do that in with one set of filters...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    That whole piece of code is a nightmare. Worse is if you need to make a slight change. Its not code that can be fixed, it needs to be re-written. If you are having issues with this, then add a new field "Line Can Be Deleted" then scan the lines and update those that can, then check if all lines can be deleted, then delete everything. Don't try to fix the code that is there.
    David Singleton
  • integrated
    integrated Member Posts: 22
    Adding a "deletable" field looks better than the nested thingy..

Categories