The first four are to use the charts of accounts built into Exact Online and the last one is the one where I find the data to build the balance sheet and P&L.
But every time I refresh the data it takes more than 10 minutes
I have reduced the data to only 5 years (2017 to 2021)
Is that normal that it takes so much time or did I miss something or did I use the wrong tables?
Is it possible to add the approximate number of rows to each table and the approximate duration in seconds?
10 minutes for one or a few companies for these is long. I would recommend the following changes:
Replace GLAccounts by either GLAccountsIncremental or optionally GLAccountsBulk.
Do not use ReportingBalance but replace it by the much faster BalanceLinesPerPeriod (when not using cost centers nor cost units) or BalanceLinesPerPeriodCostAnalysis (when needing numbers per cost center and/or cost unit).
Replace GLClassifications by GLClassificationsBulk (10x faster).
Or filter out the “Referentie GrootboekSchema” entries, which are over 10.000 lines you are probably never using per Exact Online company. Do not use GLClassificationsBulk in that case and stick to GLClassifications , but add a Power BI filter step SourceDivision is not null` directly after data retrieval (300x faster thanks to PowerBI’s query folding).