Hello, We are experiencing a problem with Navision 4.0 SP1 on SQL Server 2000 that I believe is a known issue in the Navision community. The problem is with the ApplyCustLedgEntry function in Codeunit 12 and the modification of the filter that is set on the OldCustLedgEntry variable causing the application process to be very slow at times. I have tried using another instance of CustLedgEntry to accomplish the looping, but with no success. If anyone has any suggestions or possibly their code solution it would be greatly appreciated. Thank You. Brian.
Hi, Well, what I am about to write is based on the PT 4.0 (not SP1) version and I confess not to have cross checked it against the W1 version so I am not entirely sure if this will solve your problem. I didn’t have to change CU 12 in order to correct this problem during application. What I did change was Form 232 (and Form 233, although Vendors are not as problematic as Customers, at least for our company - there’s a lot less data on the Purchases side than on the Sales side). Also, this does not relate to SQL at all as your topic implies. It’s just a bug in C/AL code. The changes were the following: Function SetApplyingCustLedgEntry There’s a CASE statement and inside the CalcType::Direct option you will find this code: IF "Applying Entry" THEN BEGIN IF ApplyingCustLedgEntry."Entry No." <> 0 THEN CustLedgEntry := ApplyingCustLedgEntry; "CustEntry-Edit".RUN(Rec); IF "Applies-to ID" = '' THEN SetCustApplId; CALCFIELDS(Amount); ApplyingCustLedgEntry := Rec; IF CustLedgEntry."Entry No." <> 0 THEN BEGIN Rec := CustLedgEntry; "Applying Entry" := FALSE; SetCustApplId; END; SETCURRENTKEY("Entry No."); SETFILTER("Entry No.",'<> %1',ApplyingCustLedgEntry."Entry No."); ApplyingAmount := ApplyingCustLedgEntry."Remaining Amount"; ApplnDate := ApplyingCustLedgEntry."Posting Date"; ApplnCurrencyCode := ApplyingCustLedgEntry."Currency Code"; END ELSE BEGIN I changed it to: IF "Applying Entry" THEN BEGIN IF ApplyingCustLedgEntry."Entry No." <> 0 THEN CustLedgEntry := ApplyingCustLedgEntry; "CustEntry-Edit".RUN(Rec); IF "Applies-to ID" = '' THEN SetCustApplId; CALCFIELDS(Amount); ApplyingCustLedgEntry := Rec; IF CustLedgEntry."Entry No." <> 0 THEN BEGIN Rec := CustLedgEntry; "Applying Entry" := FALSE; SetCustApplId; END; //DH4.00, Nelson, 2005-12-09 SETCURRENTKEY("Customer No.",Open,Positive); SETFILTER("Entry No.",'<> %1',ApplyingCustLedgEntry."Entry No."); ApplyingAmount := ApplyingCustLedgEntry."Remaining Amount"; ApplnDate := ApplyingCustLedgEntry."Posting Date"; ApplnCurrencyCode := ApplyingCustLedgEntry."Currency Code"; Function FindApplyingEntry Original code: IF CalcType = CalcType::Direct THEN BEGIN CustEntryApplID := USERID; IF CustEntryApplID = '' THEN CustEntryApplID := '***'; CustLedgEntry.SETCURRENTKEY("Customer No.","Applies-to ID",Open); CustLedgEntry.SETRANGE("Customer No.","Customer No."); CustLedgEntry.SETRANGE("Applies-to ID",CustEntryApplID); CustLedgEntry.SETRANGE(Open,TRUE); CustLedgEntry.SETRANGE("Applying Entry",TRUE); IF CustLedgEntry.FIND('-') THEN BEGIN CustLedgEntry.CALCFIELDS(Amount,"Remaining Amount"); ApplyingCustLedgEntry := CustLedgEntry; SETCURRENTKEY("Entry No."); SETFILTER("Entry No.",'<> %1',CustLedgEntry."Entry No."); ApplyingAmount := CustLedgEntry."Remaining Amount"; ApplnDate := CustLedgEntry."Posting Date"; ApplnCurrencyCode := CustLedgEntry."Currency Code"; END; CalcApplnAmount; END; Corrected code: IF CalcType = CalcType::Direct THEN BEGIN CustEntryApplID := USERID; IF CustEntryApplID = '' THEN CustEntryApplID := '***'; CustLedgEntry.SETCURRENTKEY("Customer No.","Applies-to ID",Open); CustLedgEntry.SETRANGE("Customer No.","Customer No."); CustLedgEntry.SETRANGE("Applies-to ID",CustEntryApplID); CustLedgEntry.SETRANGE(Open,TRUE); CustLedgEntry.SETRANGE("Applying Entry",TRUE); IF CustLedgEntry.FIND('-') THEN BEGIN CustLedgEntry.CALCFIELDS(Amount,"Remaining Amount"); ApplyingCustLedgEntry := CustLedgEntry; //DH4.00, Nelson, 2005-12-09 SETCURRENTKEY("Customer No.",Open,Positive); SETFILTER("Entry No.",'<> %1',CustLedgEntry."Entry No."); ApplyingAmount := CustLedgEntry."Remaining Amount"; ApplnDate := CustLedgEntry."Posting Date"; ApplnCurrencyCode := CustLedgEntry."Currency Code"; END; CalcApplnAmount; END;
Sorry nelson I should have been more specific. This is a problem with posting a process that uses the ApplyCustLedgEntry function in Codeunit 12. In our case we notice it when posting cash receipts. You can run the debugger and see Navision “hang” in different places in the ApplyCustLedgEntry function of CU 12 such as: Completed := OldCustLedgEntry.NEXT = 0; I have read that this is cause by an inefficient SQL cursor. Thanks. Brian.
Hi Brian, did you take a look at the Performance Troubleshooting Guide for MBS Navision? By using Client Monitor you could be able to have some useful information to solve your problem. I took a look at that function in codeunit 12 and I would change the currentkey used: OldCustLedgEntry.SETCURRENTKEY(“Customer No.”,Open,Positive,“Due Date”,“Currency Code”); to this one OldCustLedgEntry.SETCURRENTKEY(“Customer No.”,“Applies-to ID”,Open,Positive,“Due Date”); that is more selective since Applies-to ID is included in the index. But, I’m not a sql expert and someone told me that setcurrentkey doesn’t influence the choice of Index made by SQL Server … but I saw that using different key make sql choose different index …
Hi Brian, Silly me, I don’t know what led me to believe you were talking about application between already posted entries (which are processed using Form 232). That’s why I immediately posted this answer. Sorry for messing it up. [:)]
p.s. it would be better to create a new key: “Applies-to ID”,Customer No.",Open,Positive,“Due Date” (the first two field should be enough) and use this one instead.