Slow When Find SO and Close Find SO Window [NAV 4 SP3]

johanna

Dear all,

I am using NAV 4 SP3 Build 24219.
I open Sales Order Form. When I find Sales Order No. and close Find Sales Order window, it takes 126532 ms.
Total records of Sales Header : 483431.
Total records of Sales Line : 2426832.
I used Client Monitor to find which process that takes a lot of times.
I found in Sales Order Form - OnFindRecord(), process "IF FIND(Which) THEN" takes 29515ms, 29610ms, 22172ms, 20641ms.
What is the goal of "IF FIND(Which) THEN" ?
How to optimize process find Sales Order No. and close Find Sales Order window?
Please help. Thank you.

Savatage

483431 open sales orders? wow

ppavuk

Why do you have hundreds of thousands of open sales orders? Are your posting routines modified to retain original sales documents?

Savatage

Perhaps they are packed with quotes?

Alex_Chow

I can almost guarantee is the flowfield that's checking whether the order is late.

I've removed that flowfield calculation and the sales order flies. Of course, this is assuming you're not using the function that's related to this check.

ppavuk

Again, I'll reply here as well.

It is wrong way to store 400k records in sales header table. By design, sales header is used to store document which are 'in progress', and they should gone after posting. If your business has 400k open documents on daily (and even weekly) basis - NAV just not right product for you.

But I presume that you just broke something in posting process - as 400k of unposted sales documents - this is unreasonable. Maybe you need to review your business process.

Anyway, this is definitely not development issue.

johanna

@Savatage : no, it is including order, invoice, credit memo, and return order
@ppavuk : we use system to get shipment in sales invoice, so the sales order is not disappear..the sales order is still remain to compare the order and the shipment document.
@Alex Chow : which flowfield calculation do you removed? Do you mean field "Completely Shipped" and "Late Order Shipping" in Sales Header table?

Thank you all

andrea_djunaidy

you might want to check to the SETCURRENTKEY or SourceTableView Key.

wrong indexing could make such things occurs.
happens to me once

Alex_Chow

It's the Late Order Shipping field. Disable it in the TEST environment to see if it helps your performance. This field filters on the outstanding quantity, that's why it's so slow.

We have a client that has 10k active orders in their system with multiple sales lines. This is why I know this is a problem.

But yeah, unless you have a good business reason, 400k records on your sales header table seems excessive. You know there's a Sales Header Archiving function in NAV right?

Savatage

Ah the 'Ol "Late Order Shipping" field..

Exist("Sales Line" WHERE (Document Type=FIELD(Document Type),Sell-to Customer No.=FIELD(Sell-to Customer No.),Document No.=FIELD(No.),Shipment Date=FIELD(Date Filter)))

By disabling it doesn't you get an error on opening the form?
if so don't disable but change property from Flowfield to Normal*

*if you're not using it for anything

Alex_Chow

In the newer versions, it adds the filter to the Outstanding Quantity. :roll:

Savatage

Ah it's an UPGRADE

ppavuk

Do you ever invoice your orders?

I can't understand why they not gone at some point...

ppavuk

May I ask topicstarter how many orders do they process daily? I have a feeling that this 400k of orders are created in years and years.

Alex_Chow

I have a feeling that it's a customization where invoiced orders are kepts in the system.

Or they use the Combine Shipment process in NAV and just never ran the batch job to delete the invoiced orders.

ppavuk

Anyway, it is something unusual.

Alex_Chow

It is. But without knowing the intention, we will never find out.

I'm hoping the topic started will come back and answer our questions or at least mark the topic as solved...

johanna

@andrea_djunaidy : thank you.
@Alex Chow : yes, I know there's a Sales Header Archiving function in NAV. Thank you.
@Savatage : thank you.
@ppavuk : yes, there are some invoices for orders. we use sales invoice form, then run get shipment function. so, the sales order is not disappear. yes, you are right. This 400k of orders are created in years and years. I did not know how many orders do our customer process daily yet.

Dear all,

Thank you so much for your reply. I appreciate it so much. Now, this issue is not happen anymore. I don't know the cause yet.
Thanks.