Teamleader V2 performance improvements in server-side filtering of date columns

Wiki (en)
, ,
1

The Teamleader V2 API has a structure deviating from many other recently introduced APIs in that it does not follow OData or similar query specifications. Instead, it relies on parameterized filtering, similar to more traditional platforms such as Twinfield.

Values for such parameters can be specified in a query like:

select *
from   TimeTracking(ends_after => trunc(sysdateutc) - 7)

Most users do not specify parameters for table functions, increasing runtime of real-time Teamleader queries. The use by Invantive SQL- and Power BI-users of where clauses and, respectively, filter steps to reduce the data requested was previously not automatically translated into parameterized filters where possible. Instead, all rows where retrieved and filtering took place in the Invantive SQL-engine.

A filter that removes 90% of the data will also reduce runtime and network traffic by 90% when forwarded to the Teamleader API server.

Starting release 22.0.717, Invantive SQL will project approximately 30 types of filters on date columns in the Teamleader V2 tables on parameters for the Teamleader API. The performance improvements apply automatically without any changes required by the user.

For example, a query on TimeTracking like:

select *
from   TimeTracking
where  ends_at >= trunc(sysdateutc) - 7

would on release 22.0.716 retrieve all time records and filter them locally, whereas on release 22.0.717 it just retrieves the most recent ones, without any need for manual specifying the parameter values.

Runtime in this sample will typically decrease by two orders of magnitude, where as in many scenarios performance can even increase by another order of magnitude due to Teamleader’s strict rate limits not being exhausted in a short timeframe.

However, performance will not change in scenarios where the Teamleader API has not provided parameterized filtering for your date filters.

2

Is it also possible to optimize the between as in

select *
from v2.timetracking@tlr
where started_at between trunc(sysdate) -3 and trunc(sysdate)

to

select *
from v2.timetracking@tlr
where started_at >= trunc(sysdate) -3
and   started_at <= trunc(sysdate)

The last one is a lot faster.