The external API of Odoo ranks among industry leaders in terms of read performance, even across very large datasets. The Invantive SQL-driver for Odoo has been improved to make use of these capabilities to handle large volumes of data easily, while reducing strain on the Odoo servers.
The following improvements have been made to the driver for Odoo:
- paging, reducing time to receipt of first row,
- server-side filtering (known in Power BI as “query folding”).
Paging on Odoo
Odoo supports paged and non-paged access through the external API. Paging can reduce time till the first row of data is made available to the user.
For instance in Power BI, the Power BI Navigator upon navigation to an Odoo-table ask the Invantive SQL-driver to solely retrieve the first x number of rows (x can vary between 100 and 1000). Non-paged access requires all rows of data to be loaded before displaying a preview in the Power BI Navigator, but paged access shows the preview within seconds, while reducing both CPU, network and I/O strain on the Odoo server.
However, this also holds for typical data integration queries and especially when doing large scale data integration using Invantive’s special synchronize statement to exchange data in Odoo with (for instance) a warehouse management system.
Paging is now enabled with a default page size of 1500.
Default paging size on Odoo
An analysis was made to establish a first default setting of page size using the table event with 9390 calendar appointments on odoo.com. A number of queries were executed for each page size tested on a fast HP-workstation with a fast low-latency Internet connection:
set use-http-disk-cache false
set use-http-memory-cache false
set requested-page-size x
select count(*)
from ( select 1 c
from event
limit 10000
)
select avg(cast(duration_ms as decimal))
from SessionIos@DataDictionary
where call_safe_name like '%event'
and id between z1 and z2
The results were listed in the table below. Results include processing time by the Invantive SQL driver, but excludes row handling and other high level SQL-pipeline activities; actual API response times can be a little less. Retrieval of individual pages was measured not to be related to the page number, so the table below only lists individual page response times of the Odoo external API.
The external API on Odoo.com has no formal definition of a rate limiter besides “reasonable use” as of March 2023, but a rate limiter of 60 API calls per database per minute was deemed to be “reasonable use”.
| Page Size | Individual Page (ms) | All, no rate limit (ms) | All, rate limited (ms) | Actual (ms) | Throughput (rows/second) |
|---|---|---|---|---|---|
| 10 | 100 | 93900 | 939000 | 939000 | 10 |
| 100 | 237 | 22254 | 93900 | 93900 | 100 |
| 500 | 748 | 14047 | 18780 | 18780 | 500 |
| 1000 | 1382 | 12977 | 9390 | 12977 | 724 |
| 10000 | 12133 | 12133 | 1000 | 12133 | 774 |
The rate limiter of 60 API calls per minute restricts maximum till a certain page size level somewhere between 500 and 1000 rows per API call. The maximum throughput during the measurement was found to be around 750 rows per second. Increasing page size to 10.000 does not significantly increase performance, but increase first page response time by a factor of almost 10.
Therefore the default page size has been set to 1500 rows per page, balancing fast receipt of first rows within typically the 2500 ms boundary established for acceptable responsiveness by information ergonomics with maximum throughput.
Server-side filtering
The Odoo external API has extensive support for filtering. The SQL-driver for Odoo now supports filtering for optimal performance when only a selection of the rows is requested by the Invantive SQL query.
“Server-side filtering” forwards Invantive SQL-conditions (including data reductions sent by Power BI using “query-folding”) to the external API of Odoo. These conditions are typically listed in the where clause of the table in the from and in the on clause of a joined table. Conditions are simplified before being forwarded to Odoo. Such simplifications of SQL queries include simplification of constant expression and rewriting queries between hash joins and join sets depending on the number of data rows found in the execution plan of the Invantive SQL query.
The following conditions in SQL queries are currently being forwarded to Odoo:
- equals,
- not equals,
- less than,
- less than or equal,
- greater than,
- greater than or equal,
- in,
- not in,
- between,
- like.
Performance impact of server-side filtering
As a sample of performance improvements by server-side filtering, the following SQL queries are used as a sample. Both queries return the same result of 3 rows in the test data set, taken from a table with 9380 rows.
Odoo SQL query 1
The first query for Odoo is written such to allow application of the filters:
select *
from event
where start_date >= to_date('20210301', 'YYYYMMDD')
and stop_date <= to_date('20210308', 'YYYYMMDD')
Odoo SQL query 1
The second query rewrites the original SQL query to effectively disable the use of a filter:
select *
from event
where start_date + 1 >= to_date('20210301', 'YYYYMMDD') + 1
and stop_date + 1 <= to_date('20210308', 'YYYYMMDD') + 1
Results
The table below lists the performance improvements in response time and network bandwidth consumed as found in SessionIOs@DataDictionary:
| Query# | Response Time (ms) | Bandwidth (KB) |
|---|---|---|
| 1 | 84 | 9 |
| 2 | 12.251 | 34.769 |
Server-side filtering reduced total response time by a factor of 150, and reduced bandwidth usage by a whopping factor of 3863.
Summary
The external API of Odoo besides complete coverage of the models, also is capable of excellent read performance in terms of throughput and network usage.
Compared to other cloud platforms such as Twinfield and Exact Online, the external API of Odoo offers a very large advantage for large environments thanks to the advanced filtering capabilities across all fields without size restrictions on the filters and payload. Grouping functionality is available too, but not yet activated on Invantive SQL.
Odoo ranks with popular platforms such as AFAS Profit and Salesforce in terms of throughput and scalability.
Availability of Odoo SQL-performance improvements
The mentioned performance improvements for the SQL-driver on Odoo are available starting BETA-release 22.1.150.