1. All unique attributes [unique=true] in the header should have a database index
Consider also covering index - order of the columns is important
2. Referenced types should have a database index on their lookup value
e.g.
Unit.code and Genders.code should both have database indexes if there are a significant number of rows (more than 100) - bad example because Units and Genders had a limited number of rows but you get the idea
3. [unique=true] should normally only be added to the unique attributes in the data model
[unique=true] is not doing an validation to ensure a unique value has been used, it's only used for the WHERE part of the SELECT clause to find an existing item in the database.
4. Ensure any relations that are order and set to unordered if ordering is not required.
For product import the CategoryProductRelation is especially important. Add something like the following to your project.properties or local.properties (only available in v5)
5. Avoid mixing headers
ImpEx works best if we separate the data by header e.g.
Should be replaced with
6. Use legacy mode
Essentially this bypasses the Service Layer and uses Jalo directly. This offers a good performance improvement however we won't be able to use the Service Layer interceptors for validation.
7. Use INSERT instead of INSERT_UPDATE
The INSERT_UPDATE header will always do a SELECT query to retrieve an existing item using the unique attributes specified in the header. Sometimes we know that there is no existing data e.g. doing a data migration from an existing system. In this case we can use the INSERT header and reduce the number of queries that make it to the database
8. Use JDBC Logging to identify slow queries.
Queries should be executing in 0-5ms, any slower suggests there is a problem with performance of the application server, database server of the network IO between the 2 servers. See Logging Database Statements
9. Network IO
A low latency Gigabit link between hybris and the database is required to achieve optimal performance, 100Megabit is not good enough
10. Increase number of worker threads
If the server is a dedicated backend server start with a value between 1 and 1.5 times the number of cores e.g. if we have 4 cores, try 4-6 worker threads
Monitor the resources on the database and application server to ensure neither server is over loaded.
11. Remove unused indexes
Hybris creates a significant number of database indexes that are never used for a given project. Database indexes slow down the writing to the database so we should remove indexes that are not being used. Microsoft SQL Server, Oracle and MySQL have ways for identifying unused indexes
- MySQL - http://www.mysqlperformanceblog.com/2012/06/30/find-unused-indexes/
- SQL Server - http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/
- Oracle - http://adminoracle10g.blogspot.co.uk/2012/06/how-to-find-unused-index-in-dataabse.html
12. Update Table Statistics
Sometimes the database will not be able to optimise a query efficiently because the tables statistics are not up to date. Typically this is handled automatically by the database however on occasion the table statistics have required a manual rebuild. It's a good idea to do this after you have imported a large amount of data.
13. Disable other CronJobs while importing data
This ensures other CronJobs are not contending for the same limited resources while we are trying to import data. One's to lookout for are the Lucene and Solr indexers.
