I've been digging to find the source to as to why our Query Store reports that [PromotionInformationGetRedemptions] is taking so much performance, over time it is incredibly expensive. The marked query in the image below is the [PromotionInformationGetRedemptions] query.
This query is called from several places in epi, including the admin UI to show the numbers per promotion. But to generate this amount of load, the only place where it is interesting to look is from the PromotionEngine.Run, as this query scales with the number of visitors and is done frequently per visitor, if they modify their carts. So I went and tried to better understand the flow of PromotionEngine.Run to understand why happened so often. Skipping some things to make it simpler.
RedemptionLimitService.GetPerPromotionAndCustomerLimit(the list of promotions, customer ID, order form ID)
From the above flow I've realized what makes the query so expensive, by three things:
This is the biggest theif for us. We essentially do 2 filtering rounds. First one in step 1, filter by active, time & market. Then second one in step 6, coupon code & visitor groups.
However, in between these 2 filters, we fetch the redemption limits for all the promotions, even those that will get filtered away in the second filter wave. Seeing as we don't use the discarded promotions in the rest of the promotion engine, I assume we will never use the fetched redemption limits for the discarded promotions. The reason this gives us much load is that we have a lot of promotions behind codes, whether they are employee discounts or affiliate discounts, there are a lot of them. And many of them have redemption limits as well. These are always fetched for every customer, even if the customer has 0 coupon codes applied to the cart.
My suggestion is that you move the 4th step to after the 6th step, load the redemption limits after you've done all filtering, as the limits are not a part of the filter.
I cannot come up with any trade off with this, but I might be missing something, please reach out if so!
You heard me right! Currently it looks like when fetching the redemption count, we are actually calculating/counting it instead of fetching an already calculated value. So for each customer, for each time promotion engine is run, for each promotion, we count how many previous redemptions have been made.
This is for sure a trade off from complexity, as maintaining a calculated value increases the complexity rather than just calculating it on the fly. But when you have been running epi commerce for 4 years, the number of redemptions over the years add up. We have a lot of million entries in the PromotionInformation table, where redemptions are stored. This will also just keep to grow and become more and more expensive.
We need the PromotionInformation for other things as well, as it has valuable data, but my suggestion is to move the calculation of the redemptions away from the users and instead keep track per promotion how many redemptions have been made. Every time someone makes an order with a promotion, whenever a PromotionInformation entry is added with IsRedeemed=1, we +1 to the calculated count, etc.
Say you have a couple of hundred promotions that are active in every way except that they had a low redemption limit which has been reached. So no one will ever be able to get these discounts unless one of two things happens
My point here is that these promotions will put load on the PromotionEngine as it is currently, even though we can programatically know that it will never yield rewards. We will still go to the database and count the redemption limits for this promotion, even though we could know that nothing has changed, the redemptions are still on/above the limit.
Of the three sections, I think the ordering of filters is the one that would have by far the most impact on us if it was changed, so I'll be adding a bug report for this as well!
I will forward this to Commerce team, thank you for your detailed and insightful feedback
This issue was file as bug COM-12232, and was fixed and released in Commerce 13.28.0.