Calc and Post VAT Settlement

maca

On 2 customer sites
in NAV 2016 unmodified VAT Entry Table and Calc and Post VAT Settlement
Last quarter both ran in circa 5 minutes however this Quarter both seem to be hanging when the report modifies the VAT Entry table (closed, closed by entry no.) where the dataset > 10000 records
In Preview mode on both customer system it displays in < 1minute
On 1 site there was a SQL update.
I can only think it's a SQL issue, as it's standard NAV and has been working fine. but not sure where to start.

maca

Decided to refactor the code so it now only locks the G/L entry table for < 1min. Not really the solution but..

maca

Decided to refactor the code so it now only locks the G/L entry table for < 1min. Not really the solution but..

hrishi

Hi maca, could you please share the changes you have made, I am also experiencing similar issue.
thanks

ashmac

Basically the issue was the modifyall statement, so I put the vat entries that needed to be updated in a temp table and then updated them at the end of the process. Notice the commits, I don't like it, but solved the problem.

Example

At the end of 'Close VAT Entries'- OnAfterGetRecord



// Close current VAT entries
IF PostSettlement THEN BEGIN
VATEntry.FINDSET;
REPEAT
T_VATEntry := VATEntry;
T_VATEntry."Closed by Entry No." := NextVATEntryNo;
T_VATEntry.INSERT;
UNTIL VATEntry.NEXT = 0;
//VATEntry.MODIFYALL("Closed by Entry No.",NextVATEntryNo); //Orig Line
//VATEntry.MODIFYALL(Closed,TRUE); //Orig Line
END;


And then 'OnPostReport'


//AM 19.03.17+
IF NOT T_VATEntry.FINDSET THEN
EXIT;
COMMIT;
TotRecs := T_VATEntry.COUNT;
@1@'); //progress window, code not displaying properly
REPEAT
L_VATEntry.GET(T_VATEntry."Entry No.");
L_VATEntry."Closed by Entry No." := T_VATEntry."Closed by Entry No.";
L_VATEntry.Closed := TRUE;
L_VATEntry.MODIFY;
I += 1;
IF I MOD 100 = 0 THEN BEGIN
Window.UPDATE(1,ROUND(I/TotRecs*10000,1));
COMMIT;
END;
UNTIL T_VATEntry.NEXT = 0;
Window.CLOSE;
MESSAGE('Finished');
//AM 19.03.17-