Twinfield: faster performance with reduced data traffic and smarter handling

Wiki (nl)
,
1

The performance of Invantive’s Twinfield driver has been improved by reducing data traffic and smarter handling. There is no need to modify existing queries for this. The improvements work immediately.

The following enhancements have been implemented:

  • Only the requested columns are retrieved from Twinfield;
  • Filtering is possible on many more fields than before via server-side filtering;
  • Totals such as the number of rows are determined with one API call instead of retrieving all rows.

Under the Hood

Counting

select count(*)
from   VATCodes@twinfield

The output is:

COUNT___
15

Searching

select code
,      name
from   VATCodes@twinfield
where  code like 'V%'
order
by     code

The output is:

CODE | NAME
V19  | Sales 19% overflow
VBIN | Delivery within EU
VBUI | Delivery outside EU
VH   | Sales high
VINS | Installation/Distance
VL   | Sales low
VN   | VAT 0%
VP   | Private use

The pattern is not reported as processed. Invantive UniversalSQL applies the same condition once more afterwards. This is necessary because Twinfield searches more than one field: the pattern may be broader than the condition it originates from. A broader answer costs rows, a narrower answer loses them. A value with a literal * or ? in it therefore remains entirely within Invantive UniversalSQL, as Twinfield does not document a way to escape these characters.

Usage Visible in the Data Dictionary

Limits apply per environment at Twinfield. Therefore, SystemDataContainers@datadictionary now shows what Twinfield reports about its own rate limits per connection: the budget per minute, what remains of it, when it will be replenished, and what the last call cost.

select minutely_limit
,      minutely_remaining
,      consumption_last_call
from   SystemDataContainers@datadictionary
where  minutely_limit is not null

The output is:

MINUTELY_LIMIT MINUTELY_REMAINING CONSUMPTION_LAST_CALL
500 489 1

Thus, the measured environment has 500 calls per minute.

See also

Availability

The improvements are available from release 27.0.