0

I have multiple active shopping cart rules on my website, including some older rules. The problem is that Magento validates these rules every time there is an update to the shopping cart or when a customer proceeds to checkout.

The method setValidationFilter() calls getNoCouponCodeSelect() from the class vendor/magento/module-sales-rule/Model/ResourceModel/Rule/Collection.php, which is responsible for this validation. It goes through all the active rule_id values to check if there is a compatible rule based on the current customer's characteristics.

The SQL query that is impacting the performance in the database is as follows:

SELECT `main_table`.*,

NULL AS `code`
FROM `salesrule` AS `main_table`

INNER JOIN

(SELECT DISTINCT `salesrule_website`.`row_id`

FROM `salesrule_website`

WHERE (website_id IN (?))) AS `website` ON main_table.row_id = website.row_id

INNER JOIN `salesrule_customer_group` AS `customer_group_ids` ON
main_table.row_id = customer_group_ids.row_id

AND customer_group_ids.customer_group_id = ?

WHERE ((`is_active` = '?')

AND (main_table.coupon_type = ?)

AND (main_table.rule_id IN (?))

AND (`is_active` = '?'))AND (main_table.created_in <= '?')

AND (main_table.updated_in > '?')

ORDER BY sort_order ASC

How can I improve the performance of this query while maintaining the same business rules?

Note: I would prefer not to disable the shopping cart rules.

1 Answer 1

0

This feature, which validates shopping cart rules (sales rules) has been optimized to the maximum extent possible. As a result, you have to ensure your site doesn't have redundant rules and optimize your existing rules.

Magento (Adobe) has a related article: Best practices for configuring promotions:
"The maximum recommended total number of sales rules (cart price rules) for all websites is 1000. The maximum recommended total number of coupons for all websites is 250,000 in Adobe Commerce. Having many sales rules can have a negative impact on performance. The limitation is due to needing to validate cart contents against all rules registered in the system to apply the necessary rules.

For best performance, follow these best practices to configure sales and promotions for items in a shopping cart:

  • Sales rules (cart price rules):
    • Configure no more than 1000 cart price rules for all websites
    • Manage and remove unused rules.
    • Add strict rule conditions (like attribute or category filter) for the most efficient matching.
  • Coupons:
    • Verify that the total number of coupons in the database is less than 250,000.
    • Remove unused and expired coupons.
    • Generate only the number of coupons needed to meet campaign requirements."

References: https://experienceleague.adobe.com/docs/commerce-operations/implementation-playbook/best-practices/planning/product-cart-promotions.html?lang=en

1
  • @MageWill I don't have time to delve into this at this time. As you have an idea, you can investigate and test it.
    – Tu Van
    Commented Jun 30, 2023 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.