Increased throughput on Teamleader Focus V1 and V2 API tables

Wiki (en)
, , ,
1

Go to Dutch version

Teamleader Focus growth in reporting

Teamleader Focus has been extended and improved over the years, as well as Invantive SQL and related products such as the Power BI connector. Also, the user community of Teamleader Focus has grown to include sizeable companies and companies with a larger cumulated data volume in their Teamleader Focus database.

Many users use Invantive-based products to exchange data with Teamleader Focus, such as for reporting purposes. The limitations of the V1, V2 and new-projects APIs require a significant amount of work and tables to establish the full picture. The number of tables and data volumes of each have grown, making reports run slower.

Performance Gains

Starting release 22.0.706, a number of new performance enhancements available from both Teamleader as well as Invantive have been incorporated in the Teamleader driver. For competitive reasons, no further details are offered. The throughput has increased in many scenarios by a factor two, while in specific scenario the throughput has increased by a factor 3.

The performance improvement applies to all existing queries. No changes are required in the existing logic or reports. Actual throughput improvements vary depending on the Teamleader tables being accessed and the degree of parallelism. The performance gains are most visible when downloading data in parallel such as with Power BI of tables with require many thousands of API calls such as the V1Flat and V2Flat schemas (see also What are table functions and table function parameters?).

The performance gains with identical common hardware resources made available are displayed in the table below for both solely API V1 throughput, API V2 throughput and mixed V1/V2 tables throughput:

Variant 22.0.705 (sec) 22.0.706 (sec) Throughput Increase (%)
V1 186 60 310
V2 185 73 253
Mix 333 100 333

The performance improved release of Invantive SQL is expected to be available on Invantive Cloud before August 3, 2023.

SQL code to measure performance

Measurements were done using the following Invantive PSQL-code:

declare
  l_cnt pls_integer;
begin
  --
  -- Execute 250+ API calls to retrieve each project's 
  -- individual custom fields.
  --
  set use-http-disk-cache false;
  set use-http-memory-cache false;
  --
  select count(*) into l_cnt from (select * from teamleader.v1flat.projectsall limit 250);
  dbms_output.put_line('V1 start: ' || to_char(sysdateutc, 'HH24MISS'));
  for i1 in 1..10
  loop
    select count(*) into l_cnt from (select * from teamleader.v1flat.projectsall limit 25);
  end loop;
  dbms_output.put_line('V1 end: ' || to_char(sysdateutc, 'HH24MISS'));
  --
  select count(*) into l_cnt from (select * from teamleader.v2flat.projectsall limit 250);
  dbms_output.put_line('V2 start: ' || to_char(sysdateutc, 'HH24MISS'));
  for i2 in 1..10
  loop
    select count(*) into l_cnt from (select * from teamleader.v2flat.projectsall limit 25);
  end loop;
  dbms_output.put_line('V2 end: ' || to_char(sysdateutc, 'HH24MISS'));
  --
  -- Interleaved variant:
  --
  for i3 in 1..10
  loop
    select count(*) into l_cnt from (select * from teamleader.v1flat.projectsall limit 25);
    select count(*) into l_cnt from (select * from teamleader.v2flat.projectsall limit 25);
  end loop;
  --
  dbms_output.put_line('Interleaved start: ' || to_char(sysdateutc, 'HH24MISS'));
  for i4 in 1..10
  loop
    select count(*) into l_cnt from (select * from teamleader.v1flat.projectsall limit 25);
    select count(*) into l_cnt from (select * from teamleader.v2flat.projectsall limit 25);
  end loop;
  dbms_output.put_line('Interleaved end: ' || to_char(sysdateutc, 'HH24MISS'));
end;