How to imporve the performance of the Searching on "Completely Shipped" in sales order list?

Hi Expert.

Recently our customers feed back us a pb when they searching on "Completely Shipped" in Sales order list page, is very slowly , nearly 30 Seconds. I checked the count of records  just have 138701。How to fixed it ?

This field is a flowfield , which formula : 

Min("Sales Line"."Completely Shipped" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.),Type=FILTER(<>' '),Location Code=FIELD(Location Filter)))

I add a key "Document Type, Document No., Type,Location code " into sales line  , seems not work.

And I tried add a SIFT, "Completely Shipped" based on "Document Type, Document No., Type,Location code " , not work neither.

Any suggestion will be warmly appreciated.

BTW, My Version is Navision 2016.

 

Parents
No Data
Reply
  • This kind of filter will always be rather slow, as it needs to lookup the Sales Line for each Sales Header, but that doesn't mean it can't be optimized.

    SIFT are not used for MIN flowfieds, only for SUM flowfields, so you should not consider that here. Instead you could try with this key: "Document Type, Document No., Completely Shipped, Type,Location Code

    If that doesn't improve the performance enough, then you could experiment with a new flowfield with a different calcformula using EXIST instead of MIN. This one almost gives the same result, unless the document is without any lines, but if you only look at Released sales documents, then I doubt anyone will notice:

    -Exist("Sales Line" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.),Type=FILTER(<>' '),Location Code=FIELD(Location Filter),Completely Shipped=CONST(No)))

    (please note the "-" in front to change the "sign" of the result)

     

    If the customer is still not happy, I guess you will have to make a non-flowfield and make sure it gets updated whenever the salesline gets modified. You will have to do it using either events or OnDatabaseModify in Codeunit 1. I'l go the latter, as events are very buggy in NAV2016.

    My two final suggestion would be re-writing the list to use a query instead or disable SmartSQL on the service tier, but they both bring up a lot of new issues.

    Please let me know the results if you try any of my suggestions.

Children
Related
Recommended