Exact Online has recently announced that starting January 1, 2018 they will gradually reduce the currently unlimited maximum number of API calls per day from 50.000 to 5.000 by the end of March.
I am very happy by that decision; it will ensure that the API system remains responsive and that well-behaving apps get better response times.
And of course it makes our Data Replicator more appealing to a larger part of the user community.
Nonetheless in this article I want to give you some suggestions to reduce the number of API calls, either when you use Invantive SQL or program it yourself.
In general, there are a number of common reasons that an app executes over 5.000 API calls per company per day.
App Design
The first reason is a bad design of the session and transaction logic. This only occurs with non-Invantive SQL apps.
I’ve seen several apps continuously logging on, retrieve data from current/me and then execute a small action or pull for data. In addition to the extensive server load at Exact for the authentication flow, this causes a large number of API requests during the day.
At Invantive, we apply a simple solution to keep the number of connections low. Since our software typically runs on devices not trusted by us, we always log on using user name and password, as specified in OAuth standard not storing the client_secret on an untrusted device.
After ten minutes, the token acquired using credentials gets invalid. It is only then when the code needs to execute an API request considered unauthorized, that we acquire a new token.
When there are for instance multiple hours between API requests due to caching, this reduces the number of authorization flows extremely.
The current/me, system/divisions, hrm/divisions and available features are cached for the whole duration of our own process with this user. Especially with accountants, the number of entries in system/divisions and hrm/divisions can easily cause 50 or more API requests, so it is wise to only fetch them once per process lifecycle.
Visual Studio
On apps based upon our Invantive Bridge driver for Visual Studio, this is generally done within the driver and completely transparant to the developer. When running a webapp using Invantive Bridge, the developer must himself take care that the correct instance of our Bridge is associated with the right user. Sharing connections across users is possible, but make sure you meet the information security policy of your company, just as with a regular IIS app for Exact Online.
Power BI
For Power BI users with our driver for Exact Online, the process flow is less elegant since Power BI tears down the mashup continuously on every change. This is not a problem for an end user, but a developer is recommended to use Invantive Data Cache to keep the load low and the design process fluid.
Query Design
When retrieving data from Exact Online, you often need to combine data from various sources. For instance, you need to retrieve data for a number of items, the price of those and some transaction lines involving the items.
The brute force approach is to download all items and all transaction lines, then calculate all prices and combine the results on the PC or web server. Although often applied, this is brute force indeed. The resulting data volume are often little in compared to the data downloaded to determine them.
Do not recycle: avoid waste
As with traditional databases such as Oracle, the amount of data wasted (the “cost”) should be close to 0 for optimal performance and - in this case - keeping sufficient API requests for more work to be done.
The approach is in general similar to those applied by platforms such as Oracle:
- try to retrieve only relevant information
- change the combination (“join”) approach based upon the % of all data needed
- avoid brute force approaches and the queries with a lozenge (Dutch: “wybertje”)
In Invantive SQL, we maintain statistics on the expected number of rows in a full API result set. Based upon the filters needed, the software dynamically tries to establish whether a full scan of the API result set is preferable above queries retrieving individual elements by unique technical key (often a GUID).
Note that you can retrieve multiple individual elements by their unique technical key in one API call request. Just use the “or” construct with many GUIDs to retrieve multiple relevant rows. It seems that above 50 GUIDs the API system becomes less reliable, but that is a nice number of individual data rows to retrieve per API request, even when compared to the 60 maximum.
In general, when you expect that more than 10% of the available data rows needs to be retrieved it is better to download all rows and join them together on the local device. This percentage of 10% is not fixed; it depends also on the statistics for response time per request which you can hard-code or dynamically determine and the number of rows per request.
On Invantive SQL this problem should not surface on recent versions, since it automatically tunes your queries. However, if you need tune them manually use the “join_set” hint.
Of course, you should use caching, either in memory or on disk.
On Invantive SQL we cache the results in memory during the lifetime of the session. Using the ‘use-http-cache false’ setting, you can disable the use of memory cache for specific business scenarios.
Caching requires you to know about the business scenario which your code covers. Some elements are still “sufficiently fresh” even when weeks old, such as a list of journals, but some elements require current data.
Largely fixed lists such as Journals should be stored in a database.
With Invantive SQL you should configure the bundled Invantive Data Cache to ensure that the data is correctly cached and managed in a database.
For current data, you can also use the webhooks provided by Exact on over 30 APIs. Within minutes after an event occurs in Exact Online, you receive a notification, allowing you to either update your cache or run your business process.
With Invantive SQL you should configure Invantive Messages to get all webhook events in a local table “by magic” without running your own web server and code.
Bulk APIs
Of course, the new bulk APIs created last year have a 16x larger payload with the same number of API credits lost. The most popular bulk APIs are according to our measurements TransactionLinesBulk and DocumentAttachmentsBulk.
However, with a properly formulated query the run-time can be reduced by a factor 10 or more even when compared to the somewhat limited bulk APIs.
Restrain from table functions
Exact Online has several table functions, such as “get sales price” or “get free field value for item”. These require one API call per calculation, which for larger companies gets painful really quick.
It is best not at all to use these APIs and restrain from using more complex sales price and free fields on items when an API is needed. In some scenarios you might be able to build the pricing engine yourself using base APIs.
Maybe in the future better versions of these APIs will be made available.
Batching
When uploading data, you can combine multiple data rows in one request. That saves tremendously on the number of API requests.
In Invantive SQL we made that possible using the “attach to” and “identified by” functionality for insert statements on REST APIs.
The XML APIs are also very great at combining multiple uploads in one API request. For instance, you can upload hundreds of transaction entry lines or invoices with one request. Even better: when for instance an account on an invoice doesn’t exist yet, it is created by magic, and the same holds for contacts, addresses, GL accounts and many others.
It may be old, but the XML API is still the very fast and reliable way to load many data rows into Exact Online.
There are some size limits on the API requests. The bad thing is that large API requests can sometimes fail halfway. Sometimes a meaningful notice is returned, but sometimes it fails without further notice. You don’t know reliably what part succeeded and what part failed. Some APIs allow you to re-submit the same request and they automatically detect what was already uploaded; some don’t.
In general, I’ve learned to keep the size small. Even with a maximum of 250.000 characters they carry a lot of transactions and they still take a number of seconds to execute. So the limit of 5.000 API requests per day should not be a problem.
Desupported OData Batch Requests
Note that starting December 2019 OData batch requests have been desupported on Exact Online. All existing uses within Invantive SQL will automatically fallback to non-batched versions starting release 17.32.127 and 17.33.217 (BETA).