How to load prices into HCL Commerce 11 x faster?

Rapidly integrating prices into a shop running on HCL Commerce is crucial for many businesses, particularly those with extensive catalogs or numerous price lists received in large batches from external systems.

We analyzed how much time it will take to load 300 price lists (in the insert mode), each containing 3285 items in one currency. That gives just under 1 million price records to load in total. We started with the sample data loader's configuration provided with HCL Commerce 9.1.13. Next we just spent just over an hour on tuning HCL Commerce data loader's configuration (we did not touch any database settings).

The improvement in performance is remarkable. The time required to load 1 million records has been reduced from the original 2107 seconds (35 minutes) to just 192 seconds (3 minutes). That is 11 times less.

Implementation

How does it perform out of the box?

Out of the box when sample configuration for loading prices data is configured to use Replace mode. Since the Replace mode is not what we want for our exercise we decided to change the mode to Insert. We ran the load and it took 2107 seconds. That's a bit slow we'll try to speed it up.

Run data loader in a multiple threads

Out of the box data loader will run in a single thread. However it can be executed in a multi threaded way. We decided to run it with 8 threads. The time needed to load data decreased to 439 seconds. We've saved quite much time. Can we optimize it even more?

Adjust commit count and batch size

Next tuning parameter we should look at are batch size and commit count. We decided to change both to 500. Effects? Data load completed in 278 seconds. But we noticed that the process halted for few seconds from time to time.

Adjust heap size

Changing heap size to 2048m has helped further. We ran the load again and this time it completed in 246 seconds.

Not populating tables that are not needed

Out of the box the price mediator populates 3 tables OFFER, OFFERPRICE and OFFERDESC. Since the 3rd is hardly used in HCL Commerce we can safely skip it. We ran data load again and it helped. Result? 219 seconds.

Adjust heap size

In the last run we still saw that it seemed that JVM paused to do GC while running the load. We increased the heap size to 4096m, ran the process again and we have further time savings. This time data loader completed its job in 192s.

Summary of runs

Batch size	Commit count	Mode	Optimization	Time in seconds
1	100	Insert	-	2107
1	100	Insert	multi threaded	439
500	500	Insert	multi threaded	278
500	500	Insert	multi threaded, heap size 2048m	246
500	500	Insert	multi threaded, heap size 2048m, removed OFFERDESC	219
500	500	Insert	multi threaded, heap size 4096m, removed OFFERDESC	192

Takeaways

• The samples provided with HCL Commerce may not be optimally tuned for your specific use case.

• Working with data load samples included in HCL Commerce requires knowledge of the data loader's configuration settings and their result on data loads.

• Proper configuration of data loads can significantly reduce the time needed to process data.

If you encounter performance issues with HCL Commerce data loads or wish to learn more about tuning options, please feel free to reach out to us.