Our Managed Services team carefully analyzed the existing logic in the digital commerce transformation (DCT) product feed process and identified a major performance issue. The uploaded jobs were reading data from a custom-generated database view per channel. This resulted in an average of 20 to 25 minutes of execution time per job per channel. The massive burden placed on the database caused poor performance.
To remove the dependency on the database view generation, our team devised a solution whereby the system reads view data only once. The view data is stored in a temporary file cache on the production server. With the new solution, the first upload reads data from the view. Subsequent requests come directly from the query cache. Job execution time dropped dramatically to 120 to 150 seconds on average.
The most dramatic improvement was visible in the jobs feeding the Hybris Style table that runs 31 times per data load. In the old system, it took around 30 minutes per job, resulting in a total time exceeding 16 hours. With the new system, the same load process now takes two minutes per run, a total of 62 minutes. This represents a whopping 94% increase in performance to process inputs to this table.
Our solution provided these key improvements:
- Average job read time reduced: Each data upload job took 22.5 minutes on average under the old system. In the new system, read time dropped to an average of 2.25 minutes - a 90% reduction in average read time.
- Massive overall data upload improvement: The old system took an average of 16 hours to process all jobs for all tables. Once our solution was in place, performance dropped to an average of 3.5 hours, representing a 450% increase in speed.