Dynamics Community Forum Thread Details

Performance Issue with Report 20 "Calc. and Post VAT Settlement"

Our client ist using the following version: CH Business Central 16.1 (Platform 16.0.12630.12758 + Application 16.1.12805.0)

When using standard report 20 "Calc. and Post VAT Settlement" for one quarter we have a performance issue. Even after 10 hours the report has not finished. With the help of the SQL profiler I found the bottle neck. It is the following lines of code:

// Close current VAT entries
if PostSettlement then begin
VATEntry.ModifyAll("Closed by Entry No.", NextVATEntryNo);
VATEntry.ModifyAll(Closed, true);
end;

I realized that BC is not using the correct key on the VAT entry table. There are about 97'000 records to be closed. The table contains a total of 4.1 Mio. records. I came up with the following workaround:

if PostSettlement then begin
VATEntry.SetCurrentKey("Posting Date", Type, Closed, "VAT Bus. Posting Group", "VAT Prod. Posting Group");
VATEntry.ModifyAll("Closed by Entry No.", NextVATEntryNo);
VATEntry.ModifyAll(Closed, true);
end;

I copied the standard report and added the above key. This key is defined in the VAT entry table:

key(Key14; "Posting Date", Type, Closed, "VAT Bus. Posting Group", "VAT Prod. Posting Group", Reversed, "G/L Account No.")

{

SumIndexFields = Base, Amount, "Unrealized Amount", "Unrealized Base", "Additional-Currency Base", "Additional-Currency Amount", "Add.-Currency Unrealized Amt.", "Add.-Currency Unrealized Base", "Remaining Unrealized Amount";

}

With this workaround the report finished in about 15 minutes. However what I don't understand is why BC is not automatically using the correct key. It should not be necessary to explicitly specify the correct key. Is there any setting (maybe in the SQL server) that causes this behaviouir? I have also checked the index fragmentation for the VAT Entry table. It is around 4%.

Any help will be appreciated.

Quick Links

Leaderboard > Small and medium business | Business Central, NAV, RMS