Navision Perfomance Very Slow after upgrade from 2.6 to 4.0

Suja · 2005-11-22

Hi

Scenarion - upgrade from 2.6 -> 4.0
Server - Native Navision Database server
Problem : Perfomance slow after the upgrade to 4.0

We have recently done an upgrade from 2.6 to 4.0 and after the upgrade the perfomance is very very slow. The Posting of Sales Invoice/Sales Order realy crawls. As per some postings in the forum i have optimised the database , but the speed is still the same.

Any help is greatly appreciated

Please can some body give some lights on how to trouble shoot this with "Client Monitor"

Regards
Suja

kine · 2005-11-22

There is documentation for troubleshooting with client monitor. see MBS tools cd, Implementation\Performance Troubleshooting Guide\w1w1PerfTGuide.pdf

kriki · 2005-11-22

For the Item-, Customer-, Vendor- ledger entries, now you have an extra level, so an extra record to write.

It also depends how many indexes there are on the all the ledger entry-tables. If there are a lot more indexes, then it gets slower.

Did you remember to put the COMMIT-cache on the server?

Have you given the DB enough DB-cache? This cach MUST remain in memory, so your server must have at least 200MB more memory than the DB-cache-setting.

Have you given the clients enough object-cache? This cache must remain in memory, so your clients need enough memory

Use RAID1 and not RAID5.

Split the DB in different files that are put on different RAID1 disk-pairs.

Are you running other things on the server? Best is only Navision DB.

If you search for performance in the forum, you will get another load of tips&tricks to check.

For a Navision-DB, in general it is not a good idea to optimize the entry-tables. Optimizing makes them faster to read, but slower to write. So this is good to save DB-space and for tables that are read a lot but change very few times.

Suja · 2005-12-08

Hi

Kine/Kriki Thanks for your replies....

The Slowness while Posting the sales Invoice is solved by changing the code units pertaining to the Posting Procedures (added in key)

But now I have a problem in the Customer Card --> Ledger Entries--> Functions --> Apply Entries, then the form(232) is not opening immediately. Its searching in the Cust. Ledger Entries for very long time before opening the coresponding form. Also where ever i click in the form, it start to search in the Cust. Ledger entries again......

I have checked the keys in the Cust. Ledger Entries (upgraded)with the std 4.0 table and it seems to fine.....

Any advice....Please help....

Suja

themave · 2005-12-08

Suja wrote:

The Slowness while Posting the sales Invoice is solved by changing the code units pertaining to the Posting Procedures (added in key)

this is my biggest gripe with Navision, why is the key not added as part of the base product. Isn't any testing done on an actual user database before the code is released. Why does a user have to go through this on every upgrade, and I mean ever upgrade. It adds unneeded cost for the user, he now has to pay to determine the reason things are so slow, and then pay for the fix. in addition to having to train users on a new system, he has to listen to all the complaints about how slow it is.

done with my rant.

kine · 2005-12-08

Things are going better now, because MS SQL is main platform for Navision now and all is developed on MS SQL in MS today. It is why there are many things for MS SQL in SP1. Previous versions suffers because MS SQL server was the second one.

kriki · 2005-12-08

Suja wrote:

Hi

Kine/Kriki Thanks for your replies....

The Slowness while Posting the sales Invoice is solved by changing the code units pertaining to the Posting Procedures (added in key)

But now I have a problem in the Customer Card --> Ledger Entries--> Functions --> Apply Entries, then the form(232) is not opening immediately. Its searching in the Cust. Ledger Entries for very long time before opening the coresponding form. Also where ever i click in the form, it start to search in the Cust. Ledger entries again......

I have checked the keys in the Cust. Ledger Entries (upgraded)with the std 4.0 table and it seems to fine.....

Any advice....Please help....

Suja

I have 2 ideas that you can use to find where it is slow. First is with the debugger.
-Go to a customer ledger entry.
-Start the debugger (with breakpoints on triggers)
-Launch the Apply entries-form.
-Now go trought the code step-by-step (F8). If on a FIND,CALCSUMS,CALCFIELDS, it takes the debugger some time to go to the next command, you have found where the problem is. Here some filters and the key are not OK. Here you have to change it.

The second is with the client monitor:
-Go to a customer ledger entry.
-Start the Tools=>Client Monitor (Options=>"Include Object Table activity"=FALSE because you don't need this)
-Launch the Apply entries-form.
-If it already is slow in this, after it finished searching, go back to the client monitor and stop it. (otherwise do your action that is going slow and after it is done, stop the client monitor)
-Put a filter on "Parameter No"=100 (it is "Elapsed Time (ms)").
-Search in column "Number" a record with a high number. Go to the record and remove the filters.
-Now you see the table involved, the index and the filter. Now if the first field(s) of the index has/have NOT a good filter on it/them (meaning an equal filter or a from..to filter), this can go slow. See where this is used and change the code or properties (changing key).

kriki · 2005-12-08

kine wrote:

Things are going better now, because MS SQL is main platform for Navision now and all is developed on MS SQL in MS today. It is why there are many things for MS SQL in SP1. Previous versions suffers because MS SQL server was the second one.

Yes, but I heard/read (didn't check though) that they didn't change the existing code to use the new things SP1 offers. If there is a problem, you still have to do it yourself.

Paul_Bradbury · 2005-12-08

Suja,

I had a similar problem in form 232. I found that the form contains two instances of SETCURRENTKEY("Entry No."); which are executed prior to adding a filter on "Entry No." to ensure the applying entry is not shown in the list of entries. I commented these lines out and all worked fine - the correct key was retained on the form.

Hope this helps.

Paul.

Suja · 2005-12-09

Hi All

Thanks a lot for all your suggestions...I realy appreciate that and it has helped me to solve my issue...

I tried first with the Client Monitor as Kriki suggested, and found that more elapsed time was taken by the code as follows

On Form 232-
Control --> CalcApplnRemainingAmount

On Format()
IF NOT ValidExchRate THEN
Text := Text000;
ValidExchRate := FALSE;

I was stuck as don't know how to proceed....

Later i read Paul's suggestion and tried by disabling the SETCURRENTKEY on "Entry No." in SetApplyingCustLedgEntry and FindApplyingEntry Function and it worked fine. Now the searching/slowness is gone.....

Paul,
Have you set any additional key/coding on the 232 form after disabling the SETCURRENTKEY on "Entry No." as you have suggested...

Thanks a lot

Suja

Paul_Bradbury · 2005-12-09

Suja,

No I didn't. As far as I can see they have inserted the SETCURRENTKEY("Entry No.") command because they have added a new filter on the "Entry No." field, so that the applying entry is not also shown in the list. However the existing filters (on Customer No. and Open) are still in force so the original key (Customer No, Open, Positive etc) is the best one to use.

Therefore just commenting out those two SETCURRENTKEY lines is sufficient. The problem also exists in Form 233 but probably doesn't show itself as you don't tend to have as many vendor ledger entries as you do customer ledger entries.

Regards

Paul.

rkaufmann · 2005-12-20

@Suja
@themave

Hi,

you are takling about adding a key(s) and making changes to codeunits to solve a performance issue while posting sales orders and invoices.

Can you please tell me, which key you added, and which changes you did to the codeunits?

We have a customer over here with exactly the same problem.

Update from 2.60 to 4.0.
Commit-Cache on server is active. DB cache is set to max (nearly 2GB), server has 4GB RAM.
The Server is hosting only the Navision DB, nothing else.
The DB is split into 14 Parts (14 RAID1 disk-pairs).

Thanks,

Rolf

Suja · 2005-12-22

Hi

Sorry for the late replies, i was quiet tied up at the customer site.

Hi Paul, Thanks a lot for the suggestion. It helped me to solve my issue

Hi Rolf,

Let me explain the scenarios I have faced and the steps I have gone through which ultimately made the Perfomance (atleast)better..

I haven't applied SP1 to my upgraded Database yet. So I faced an issue with "Apply Payment Entries". (This issue is being solved in SP1)The Issue was in Standard Navision V4.0, if payment journal is applied (with Applies-to id indicated) but not posted, and after that, if i go and post some of my O/S invoices , the applied-to id will be cleared by the system. This means that system will clear the applies-to id and without double checking if user post the payment journal, the payment will not be applied against the invoices.

To Solve this Microsoft sent us a hotfix, where modifications was made to Code Unit 12- Gen. Jnl.-Post Line. After I applied this hotfix, i found the perfomance was a bit better, but still have to be improved mainly at the last part of Posting Sales Invoice (Posting to Bal. Account). I used Client Monitor and found the Code Unit - 12 , PostCust() function is the one taking more "elapsed time". I found in that function, at the last part, they did SETRANGE to Applies to Id, Customer No without setting appropriate Keys. So new Key with Applies to Id, Customer No has been created in Cust. Ledger Entry table and added to the last part of the PostCust function in Code Unit 12 which improved the perfomance..

Hope this helps you too... If you haven't applied SP1, and want the hotfix for the Apply Payment Issue (if you have), sent me the mail id, i can pass to you. Am not sure whether the hotfix is the same for your localised version...

Regards
Suja

rkaufmann · 2005-12-22

Hi Suja,

sorry to say, but in the function "PostCust" in CU 12, there is no SETRANGE or SETFILTER to the fields you mentioned.

The version list entry for CU 12 is "NAVW14.00,NAVDACH4.00,AR3.70,CO4.00.01" (we are using the german version).

Could you just send me a code sample of what you changed, maybe I just to blind to find the code to change.

Thanks,

Rolf

Suja · 2005-12-22

Hi Ralph

Below see the PostCust function at the codeunit-12. Please note that in the Apply Payment Hotfix which i got from Microsoft, this Code unit is midified/enhanced. So am making changes on the top of that...

IF GLSetup."Enable Tax Invoices" THEN BEGIN
IF (GenJnlLine."Applies-to Doc. No."<>'') OR (GenJnlLine."Applies-to ID"<>'') THEN BEGIN
CASE "Document Type" OF
"Document Type"::Payment:
BEGIN
TaxManagement.ApplyCustInvoiceWHT(CustLedgEntry,GenJnlLine1);
END;
"Document Type"::Refund:
BEGIN
TaxManagement.ApplyCustCreditWHT(CustLedgEntry,GenJnlLine1);
END;
END;
END ELSE BEGIN
ReportSelection.RESET;
ReportSelection.SETRANGE(Usage,ReportSelection.Usage::"S.TaxInvoice");
IF ReportSelection.FIND('-') THEN BEGIN
REPEAT
SalesTaxInvHeader.SETRANGE("Posting Description",GenJnlLine."Document No.");
IF SalesTaxInvHeader.FIND('-') THEN
REPORT.RUN(ReportSelection."Report ID",SalesSetup."Print Dialog",FALSE,SalesTaxInvHeader);
UNTIL ReportSelection.NEXT=0;
END;
END;
END;

//#1.1+
CustLedgEntry.SETCURRENTKEY("Applies-to ID","Customer No.");
CustLedgEntry.SETRANGE("Applies-to ID",GenJnlLine."Applies-to ID");
IF GenJnlLine."Account Type"=GenJnlLine."Account Type"::Customer THEN
CustLedgEntry.SETRANGE("Customer No.",GenJnlLine."Account No.");
IF CustLedgEntry.FIND('-') THEN
CustLedgEntry.MODIFYALL("Applies-to ID",'');
END;
END;
//#1.1-

Regards

Suja

rkaufmann · 2005-12-22

Hi Suja,

thanks for your help.

But your version of CU 12 seems to be completly different from our version.

Bye,

Rolf

wheresjustin · 2005-12-23

There is a very similar issue in Codeunit 12 in the PostVend() function:

VendLedgEntry.SETRANGE("Applies-to ID",GenJnlLine."Applies-to ID");
IF VendLedgEntry.FIND('-') THEN
VendLedgEntry.MODIFYALL("Applies-to ID");

Posting a PO is taking me 15 minutes when it gets to the MODIFYALL.

dsatria · 2007-06-08

Suja wrote:

//#1.1+
CustLedgEntry.SETCURRENTKEY("Applies-to ID","Customer No.");
CustLedgEntry.SETRANGE("Applies-to ID",GenJnlLine."Applies-to ID");
IF GenJnlLine."Account Type"=GenJnlLine."Account Type"::Customer THEN
CustLedgEntry.SETRANGE("Customer No.",GenJnlLine."Account No.");
IF CustLedgEntry.FIND('-') THEN
CustLedgEntry.MODIFYALL("Applies-to ID",'');
END;
END;
//#1.1-

Hi Suja,

Does the hotfix contain fixes to other areas as well? PostVend() perhaps?
Could you share the hotfix with us? We have same performance issue with CU 12 here...

Thank you in advance

Howdy, Stranger!

Quick Links

Categories

Navision Perfomance Very Slow after upgrade from 2.6 to 4.0

Comments