Faster way to retrieve account balances on Fortnox

Vragen (en)
, , ,
1

I am using the following view to retrieve the account balances:

create or replace view AccountActiveBalances
as
select act.Number AccountNumber
,      act.Description AccountDescription
,      act.Active AccountActive
,      act.Year AccountYear
,      anr.Account_BalanceBroughtForward BalanceBroughtForward
,      anr.Account_balanceCarriedForward BalanceCarriedForward
from   Accounts@fnx act
join   Fortnox.Accounts.AccountByNumber@fnx(act.number) anr
where  act.Active = true

But it is very slow, like 10+ minutes, for 558 active accounts.

Is there any way to retrieve the balances in a more quick way, say within 30 seconds?

2

An analysis has been made. It was not possible to find an alternative API to retrieve the account balances in larger batches than one. To be certain, a request has been made to Fortnox API Support.

As an alternative, it is being studied whether the processing can be done in parallel. Given a rate limit of 25 API calls per 5 seconds, the absolute minimum duration would be 112 seconds.

3

Fortnox API Support was so nice to suggest to switch to SIE when many accounts are needed:

  • The list GET at the moment only gives balance brought forward and not carried forward.
  • The recommended approach to get the full list of accounts and the balances is by calling SIE with type 1. This file contains in and outgoing balances and the account plan.

The structure of a SIE file is described at Format – Föreningen SIE-Gruppen

We will look into ways to make this possible and improving performance considerably for account balances.

4

Try this approach: in our case script runs for 163 seconds for 623 entries:

create or replace table accounts@inmemorystorage
as 
select * 
from   Accounts@fnx
where  Active = true;

create or replace table account_balances@inmemorystorage (
  AccountNumber varchar(20),
  AccountDescription varchar(100),
  AccountActive boolean,
  AccountYear integer,
  BalanceBroughtForward decimal(18,2),
  BalanceCarriedForward decimal(18,2)
);

begin
  for r in 
  ( select Number
    ,      Description
    ,      Active
    ,      Year 
    from   accounts@inmemorystorage
  )
  loop
    execute immediate 'insert into account_balances@inmemorystorage
    (AccountNumber, AccountDescription, AccountActive, AccountYear, BalanceBroughtForward, BalanceCarriedForward)
    select ''' || r.Number || ''', ''' || r.Description || ''', ' || 
    case when r.Active then 'true' else 'false' end || ', ' || r.Year || 
    ', anr.Account_BalanceBroughtForward, anr.Account_balanceCarriedForward
    from Fortnox.Accounts.AccountByNumber(' || r.Number || ') anr';
  end loop;
end;

select * from accounts@inmemorystorage;

select * from account_balances@inmemorystorage;
5

Thanks for the suggestion. This is indeed a manual solution to acquire maximum throughput.

The next step is to investigate parsing the SIE file format, since it is capable it seems to return all balances in approximately 1 second, which is approximately up to 100 times faster.