A category of queries with limit is faster and uses less data traffic, because the requested number of rows is provided to the platform instead of being applied afterwards (server-side filtering). There is no need to modify queries; the improvements are immediately effective.
Smaller request; higher performance
The data volume is greatly improved by retrieving fewer rows, and often the duration is also significantly better compared to release 26.0. The number of rows is included in the request sent to the platform.
A relational database receives the filter as part of the statement (depending on the platform as limit, fetch first or top (n)). An API receives it as part of the request, for example as $top. The rows that are thus eliminated are not constructed, sent, or read by the platform.
On GLAccounts of Exact Online, limit 3 retrieves 4,670 bytes per administration in 297 ms, compared to 92,940 bytes and 1,356 ms without that limitation, and no second page is required compared to previously needing 4 API calls:
select *
from ExactOnlineREST..GLAccounts@eol
limit 3
On a PostgreSQL table with 1.7 million rows, the volume also reduces accordingly, and the execution duration decreases from 87 seconds to 125 milliseconds:
select *
from table@postgresql
limit 3
First impression of *Incremental table of Exact Online
Retrieving an impression of an incremental table (*Incremental) by requesting the first few hundred or thousand rows becomes up to thousands of times faster. This is a common scenario for first-time use, for example with the Navigator in Power Query and Power BI.
Determining the first row of an incremental table such as TransactionLinesIncremental is expensive: the entire dataset must be fetched and updated. With a limit of up to 2,500 rows, the rows are now fetched directly from the synchronisation endpoint. This requires one call per administration for every 1,000 requested rows and takes a few seconds, instead of first retrieving the entire administration.
On a TransactionLinesIncremental table with 650 thousand rows, divided over 5 parties, execution decreases from 867 to 2 seconds if the caches (if the caches are not yet loaded):
select *
from TransactionLinesIncremental@eol
limit 1000
When the filter stays within Invantive UniversalSQL
If the rows are needed before the limitation can be applied, Invantive UniversalSQL applies the limitation after the rows have been retrieved. This occurs, among other cases, when sorting on a column the platform cannot sort on, with a condition the platform cannot calculate itself, and with a data collection that is combined further along in the query.
Availability
The new functionality is available from release 27.0.