AI OnAI Off
Inventory Service for commerce Save Method
4 hours for 35k inventory updates is a bit ... extreme I would say. The inventory system is supposed to be very fast - as the update operations are quite simple in terms of SQL. The time might actually be spent in serializing data and transfer it over network. How many entries and inventory records you have in total?
As a rule of thumb we recommend to update in batches. It depends on many factors, but I think 10-50 entries per batch (i.e. 70-350 inventory records) should be reasonable. You might need to do a bit of tuning to decide which size works best for you.
Aug 30, 2017 19:15
Sorry the 4 hours was for the total job to run. We have about 150k item variations with 7 warehouses coming out to around 1 million inventory updates that need to be made. We were batching at 5000 variations at a time which could yield up to 35k inventory record updates per batch. I lowered it to 50 inventory records per save call which has dramatically improved the performance of the job. Throwing 35k Inventory records per save call was the main issue, I could see that being an issue for serialization. But the most interesting behaviors we were seeing was that the site would essentially go down saying there were no available connections to the database left to handle the request.
Aug 30, 2017 20:58
Hello,
I recently ran into an issue with the Save method for the IInventoryService for Episerver Commerce. The project I am working on has an Inventory update job that runs every night for the clients entire catalog. Recently we have noticed that it has been silently failing. A detail that caught my eye was the fact that we were saving up to 35k inventory updates spread across 5000 catalog entry codes. This is because they have 7 warehouses they can ship from. This was apparently causing locking issues on the database because we were updating any given catalog entry code 7 times but also pushing 35k items that needed updating in once instance. We would see this job run for about 4 hours. The solution was to save smaller portions of these inventory records. This actually brought down the job's run time to about 25 minutes max. Without this update, our test environment was preventing other db connections from being opened (max pool count reached). I was curious if the Save call that's part of the IInventoryService spawns extra connections to the Database depending on the size of the list of Inventory Records being pushed to it. Anyway the lesson here is send smaller payloads to the Save call! I Just thought I would bring this up as maybe a possible improvement for the IInventoryService Save method. I am not sure how it runs behind the scenes now but it seems like the larger the payload the more trouble it has This is amplifeid by having multiple warehouses per Catalog Item.
Cheers,
Sean Heider