Commerce batching performance – part 2: Loading prices and inventories

UPDATE: When looked into it, I realize that I have a lazy loading collection of entry codes, so each test had to spent time to resolve the entry code(s) from the content links. That actually costs quite a lot of time, and therefore causing the performance tests to return incorrect results. That was corrected and the results are now updated.

In previous post we talked about how loading orders in batch can actually improve your website performance, and we came to a conclusion that 1000-3000 orders per batch probably yields the best performance result.

But orders are not the only thing you would need to load on your website. A more common scenario is to load prices and inventories for product. So If you are displaying a product listing page, it’s quite common to load prices and inventories for all products in that page. How should it be loaded?

Time to write some code to test it. By using IPriceService on a fairly big category (with about 2200 entries), I will try to load prices by different batch sizes to see which perform best. The entry codes will be pre-loaded and the cache will be cleared after each run

That’s … interesting. Loading by batch of 10 is actually slower than loading by each, and not just significantly, it’s magnitude of 3 folds. That’s contradict to the common belief that by loading by batch, we reduce the overhead of database connections and therefore reduce the time. The test time then actually reduces when the batch size increases, as we expected. What could be wrong here?

Unless I realize I forgot to clear cache after each run. Big mistakes! We run each test 10 times, so even clearing cache after each test, then the 9 subsequent runs will still be accessing the cache! If you load by batch of 10, it will still caches prices for each entry individually, then next time when you load another batch, it will check if any of the batch is already cached and only load the uncached from database., so it is super fast in subsequent runs. That explains why “loading by each” is faster than “loading by batch of 10”.

A corrected result looks like this:

This time it makes more senses, loading by batches is faster, and it is faster when the batch size increases. Unless you have a very, very big category (which usually a bad thing), you can just load everything at one go and be done with it.

What’s about inventory, we have almost same story here:

(Loading by batches of 10 is actually slower – I had to run several times to make sure it is not just a hiccup. This will be very interesting to look into)

But – even with loading prices and inventories by batch, is it really best way to get prices and inventories? Yes, if you are updating them then that makes senses. However if you just want to display a product listing page, do we really need to load prices and inventories?

That’ll be the topic for the next blog post!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.