Improving Openbravo Performance
For Openbravo Product Development Team, performance has been (and it continues being) one of our most important focus during the last months.
Performance is a broad topic and we have been working hard to improve it across different areas. This post highlights the most relevant projects to improve performance we have been delivering during the last year, being each of them by itself a good reason to update Openbravo ERP Platform to its latest release.
Available from MP24
Openbravo provides out of the box datasources for all the entities defined in the system. Entities can be defined based on database tables and views. These datasources can be used in Pick and Execute windows to easily generate a rich UI allowing to select records to execute processes on them.
The problem comes when data for this datasource requires a complex query to be executed, sometimes it is not possible to create a database view that correctly performs filtering by some context variable.
In these cases it is possible to create a manual datasource where the query is created at runtime based on this parameters, being much easier to properly optimize it.
Stock Reservation and Return to Vendor windows that were reported as slow under some circumstances were refactored making use of this new feature (these refactors are available from PR14Q2 release). The improvement made to these windows change them from taking several minutes to load data to be possible to comfortably work with them.
Available from MP27
Openbravo provides REST XML and JSON webservices for all entities defined in the system. In many occasions when data is fetched, it is only required to retrieve some of the properties available but not the rest. This capability allows to define which are the properties to be obtained.
Reducing in this way the number of DB queries needed to get the data in the backed as well as computation time to generate the response. It also can save lots of network bandwidth and resources required to deal with the response.
For this project we prepared some metrics, all of them based on retrieving 100 orders and restricting to 5 columns in the header:
- Before the project, when all properties were fetched it took 690ms returning 43.5KB, after it has been reduced to 440ms (36%) and 25KB (42%).
- Retrieving also lines, it was reduced from 17s and 380KB to 1.2s (92%) and 49.3KB (87%).
Available from MP27
Computed columns allow to define properties based on SQL expressions. This is a useful mechanism to display within a window information related to a record that can be somehow computed. The problem with the initial implementation is this SQL was executed by Hibernate for each single record matching the criteria even before applying record range limits, creating two performance issues:
- Standard Openbravo grids are paginated, meaning that when having thousands of records at Database, only some of them are retrieved each time, saving computation and network costs. Before this project, computed columns’ SQLs were executed for all records matching filter criteria in grid even they were not retrieved within the page.
- In many occasions, mainly when coding, computed columns values are not necessary to complete the flow. In these cases, the query is no longer executed.
Note that starting from MP29, these two improvements can be combined for grids were the computed column is not shown, completely preventing execution of all the queries for computed columns.
The improvement obtained by this project depends the number of computed columns, their queries and the amount of data in that entity.
Testing it with the standard Sales Order window (which has two computed columns) having around 120K records and displaying the computed columns in the grid. Time to retrieve data to populate the grid decreases from about 6 seconds to less than 1 second which is around 84% faster.
Available from MP27
It is topic well known that database queries in order to properly scale require to have indexes in the proper places.
In Openbravo the most standard data visualization is parent-child tabs. So we know that entities displayed in child tabs are commonly filtered by the foreign key linking the parent one. After reviewing our code, we realized a common pitfall developers do is to forget to add this kind of indexes.
We added them (around 140) and included in our Continuous Integration a test to guarantee this does not happen again in the future.
Difference between doing a sequential scan or an indexed one in a table with hundred of thousands of records can be from several seconds to few milliseconds.
Available from MP28
Requests to populate grid in standard windows are always paginated, with a default page size of 100 records. This allows to have a better performance when the amount of data is bigger than 100 records.
When filtering or sorting on grids, additional requests are needed to retrieve the new data. Before MP28, these additional requests were always triggered. After MP28, new logic has been added:
- Sorting: if the whole dataset (with current applied filters) is already in client, sorting can be executed in the browser.
- Filtering: when filter criteria is changed, it is compared with the one used to retrieve data. In case the new one is more restrictive than the previous one and the previous one retrieved all the data without extra pagination, filtering can be computed in the browser
This capability saves tons of backend calls, reducing server resources as well as network traffic.
For example, having 1000s of Orders. 90 created during last day. You want to filter the ones created since yesterday for Business Partner John Smith in draft status with a gross amount bigger than 100€.
Requests previous to MP28:
- Filter by date
- Filter by John Smith Business Partner
- Filter by Draft Status
- Filter by amount >100
In total, at least 4 requests.
Requests after MP28:
- Filter by date (goes to backed and retrieves 90 records)
- Filter by Business Partner (in browser)
- Filter by Draft Status (in browser)
- Filter by amount >100 (in browser)
In total 1 request to backend, being the rest computed in browser. Saving 75% of requests, in this case.
Available from MP28
Update Database is executed whenever Openbravo is updated to a newer release or a module is installed. This is the task in charge of updating database structure to the new one keeping data.
Three main improvements have been implemented in this process
- Queries executed to calculate default values have been optimized.
- Cascade deletions were executed for all tables but they only should be applied to Application Dictionary tables, were volumes are much smaller than in transactional tables
- All foreign keys were recreated, this is only required for Application Dictionary tables.
Tested in an actual customer with big volumes each update database has been reduced from more than 2 hours to 12-15 minutes, being 90% faster.
Available from MP29
Before MP29, whenever data for any grid was requested all columns in the entity behind were retrieved within the returned JSON object, including also those columns that hidden.
This project removes from the object hidden columns, improving performance in different ways:
- Foreign Key columns require extra DB queries to obtain identifier of foreign record.
- Computed columns require extra DB queries.
- JSON object is smaller, saving in this way backend resources to generate it as well as network bandwidth.
Testing average response time and response size for all grids in Openbravo 3 distribution, these are the improvements:
- Server side average response time, decreased from 141 to 41ms (72% improvement).
- Average response size: from 52KB to 26KB (improved in 50%).
Available from MP31
Openbravo offers a rich grid UI which allows to sort and filter as you type by any column. Even this is great and works fine in many cases, in some others, with big volume of data, it requires database to be fine tuned for each specific column that is going to be filtered or sorted.
The goal of this project is to allow to define restrictions for these two capabilities, so system administrators can define, case by case, where filters and sorting can be applied:
- Sorting: it can be defined whether sorting by any column is allowed or not at system, tab or column level
- Filtering: configuration applies to the same levels system, tab or column.
- It is possible to define whether it is possible to filter or not.
- Filter can be defined to be applied while typing or by user’s request.
- Filtering behavior can be configured defining whether it is case sensitive or insensitive and if filter is applied as contains, starts with or equals.
This capability allows to configure the system in a way that prevents execution of queries that are known to take long time to be executed.