Projects:Data Access Layer/DAL Performance
Contents |
Overview
This page gives an overview of the results of performance tests which read and update larger sets of data. These performance results give an idea of what can be expected when working with the data access layer. However it is very important to realize that performance can depend on many things (network, data structure, transaction handling, database, memory, concurrency/locking, etc.). The performance results on this page can therefore only be used to get a feel for a possible performance but can not be used to predict performance in other situations or with other system configurations.
The performance tests only test the direct data access layer performance: objects are read from the database and information is printed to the console. Note that printing to the console is a fairly expensive operation. The performance tests do also perform security checks but no other parts of the Openbravo ERP system (like the UI) are tested.
The performance runs have been executed in the following environment:
- 2.5Ghz dual core processor, 4Gb memory laptop
- Postgresql 8.3.1 database (standard installation, no tuning done)
- Openbravo 2.5
- Single user, as junit testcases
The performance testcases can be found in the src-test folder in the org.openbravo.test.dal package.
Read Performance
The read performance tests read data from the system and prints information to the console.
Read All Products and print Referenced Information
This testcase reads all 75000 products and prints the product identifier and the identifier of the product TaxCategory and ProductCategory. Fetching the TaxCategory and ProductCategory is not optimized so Hibernate will do additional select statements (per product) to get this information. On the other hand the TaxCategory and ProductCategory are cached in the Hibernate session. So very quickly they are not fetched from the database but from the first level cache.
Performance result 1: reading 75000 products and displaying information in the console takes 48.7 seconds (48731 milliseconds). Performance result 2: reading 75000 products and not printing to the console takes 27 seconds (27098 milliseconds)
Read Products in batches of 25 Products/Page
This performance test first counts the number of products in the system (in the test database there are 75000). Then it reads the products in a paged mode. With one query and one transaction per page. The products are sorted by name. For each product the identifier is printed to the console.
The measured performance is that reading all products (3000 pages, 3000 queries, 3000 transactions) takes an average of 86 milliseconds per page of 25 products.
Read Products in batches 25 Products/Page, read referenced information
This performance test is the same as the previous one with the difference that next to the product identifier also the names of the TaxCategory and ProductCategory are printed to the console. The fetching of the TaxCategory and ProductCategory is not optimized so Hibernate will do additional select statements (per product) to get this information. On the other hand the TaxCategory and ProductCategory are cached in the Hibernate session. So very quickly they are not fetched from the database but from the first level cache.
This performance test result: 96 milliseconds per page of 25 products.
Update/Create Performance
Update all products
In this testcase all products (75000) are read from the database, the name of the product is changed and then is saved. The whole test is done in one transaction and one query is used to retrieve the products.
The performance result: updating 75000 products took 116534 milliseconds (around 107 seconds, so around 700 products/second)
Update all products by page
This test also reads all products from the database and changes their name and saves them. The difference with the previous test is that the products are read by page of 25 products. The products are sorted by name. Each page update is done in a separate query and transaction.
The performance result: 110 milliseconds per page of 25 products.
Create InventoryCount Header and Lines
This performance test tests the creation of a header and lines and then persisting this business object in the database. In this test 5000 InventoryCount headers are created with each 10 InventoryCountLines. This is done within one transaction.
This test was done when the InventoryCount table had around 10000 records and the InventoryCountLine table had 180000 records.
The performance result: 246.8 seconds (246836 milliseconds)
Read InventoryCount Header, add one line, update one line
In this performance test the results of the previous test are read from the db and one new line is added to the InventoryCount header and one line is updated.
This test was done when the InventoryCount table had around 15000 records and the InventoryCountLine table had 230000 records.
The performance result: 17.6 seconds (17609 milliseconds)