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
The test 'Load price by each' ran 10 time(s) successfully. Took: 5954 ms. Average: 595 per run The test 'Load price by batch of 10' ran 10 time(s) successfully. Took: 18481 ms. Average: 1848 per run The test 'Load price by batch of 20' ran 10 time(s) successfully. Took: 8411 ms. Average: 841 per run The test 'Load price by batch of 50' ran 10 time(s) successfully. Took: 4966 ms. Average: 496 per run The test 'Load price by batch of 100' ran 10 time(s) successfully. Took: 4072 ms. Average: 407 per run The test 'Load price by all' ran 10 time(s) successfully. Took: 2650 ms. Average: 265 per 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:
The test 'Load price by each' ran 10 time(s) successfully. Took: 28761 ms. Average: 2876 per run The test 'Load price by batch of 10' ran 10 time(s) successfully. Took: 6574 ms. Average: 657 per run The test 'Load price by batch of 20' ran 10 time(s) successfully. Took: 4813 ms. Average: 481 per run The test 'Load price by batch of 50' ran 10 time(s) successfully. Took: 3094 ms. Average: 309 per run The test 'Load price by batch of 100' ran 10 time(s) successfully. Took: 2680 ms. Average: 268 per run The test 'Load price by all' ran 10 time(s) successfully. Took: 2704 ms. Average: 270 per run
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:
The test 'Load inventory by each' ran 10 time(s) successfully. Took: 19436 ms. Average: 1943 per run The test 'Load inventory by batch of 10' ran 10 time(s) successfully. Took: 2332 ms. Average: 233 per run The test 'Load inventory by batch of 20' ran 10 time(s) successfully. Took: 1534 ms. Average: 153 per run The test 'Load inventory by batch of 50' ran 10 time(s) successfully. Took: 850 ms. Average: 85 per run The test 'Load inventory by batch of 100' ran 10 time(s) successfully. Took: 623 ms. Average: 62 per run The test 'Load inventory by all' ran 10 time(s) successfully. Took: 400 ms. Average: 40 per run
(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!
