Dynamics Community Forum Thread Details

Using temporary tables for report speedup

Hello,

I am currently working on a complex report that processes a large amount of data (the report traverses the VAT Entry table), and I want to speed it up. For every data item in the report, I have to perform calculations that depend on data from a setup table. This "setup table" has multiple records (for each combination of VAT identifiers) but no more than about 100. My idea is to copy this setup table to a temporary table in the OnPreReport trigger and retrieve records from this temporary table, instead of reading from the database every time. Is this a good idea?

trigger OnPreReport()
var
    _VATReportView: Record VatReportView;
begin
    _VATReportView.SetRange(Type, TempVatReportView.Type::Sales);
    if _VATReportView.FindSet() then
        repeat
            TempVatReportView.Init();
            TempVatReportView.TransferFields(_VATReportView);
            TempVatReportView.Insert(false);
        until _VATReportView.Next() = 0;
end;

Other thing that is bothering me is what would be the best practice for reading data form temporary table multiple times (for each data item)? Does FindSet works as expected?

trigger OnAfterGetRecord()
begin
    // Should I Use TempVatReportView.Reset here?
    TempVatReportView.SetRange(FieldA, Dataitem.ColumnA);
    if TempVatReportView.FindSet() then
        repeat
            // ....
        untill TempVatReportView.Next() = 0;
end

Categories:

Business Central

Quick Links

Leaderboard