Heya!

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.

1. Load all promotions that could give us a reward. Normally, everyone that is active, within the time span and in the correct market.  
2. Iterate through all order forms (In my case we only have 1, so I'll keep it flat)
3. Remove the lineitems that should never get a promotion for the cart, temporarily while running the engine.
4. Load the redemption limits for all promotions fetched in step 1. By doing this, the engine ultimately calls RedemptionLimitService.GetPerPromotionAndCustomerLimit(the list of promotions, customer ID, order form ID), which:
   1. Loads the customer from the ID on the ordergroup to see if it exists
   2. Filter out the promotions that have set a Total (PerPromotion) or Per Customer redemption limit. I.e. only keep those, discard the rest.
   3. If we didn't find a customer, from the output from above, remove all the promotions which has a Per Customer limit, as you need to be a customer in order to have a chance to get these promotions at all.
   4. Calculate and load the redemptions from the database, calling our PromotionInformationGetRedemptions
   5. For each promotion, do some logic to compare the fetched redemption figures to the limit that was set on the promotion and return a result from this.
5. Create the promotion context
6. Run the PromotionFilters on the promotions returned from point 1. This will remove promotions that are behind a coupon code that doesn't exits in the cart and promotions that are behind visitor groups.
7. Loop through the filtered result from point 6 and try add them to the cart by using the processors and that whole schebang.

From the above flow I've realized what makes the query so expensive, by three things:

The order of filters

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! 

Counting the redemptions

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.

Fully redeemed promotions are still active

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

• The promotion is saved with a new redemption limit that is higher than the previous one
• An order is cancelled, subtracting a redemption

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!