Performance of the Delete Invoiced Sales Order

integrated

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?

idiot

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.

integrated

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

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...

David_Singleton

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.

integrated

Adding a "deletable" field looks better than the nested thingy..