Jump to content

Adding 21,000 discount codes to cart rules


leonetrek

Recommended Posts

I added 21,000 discount codes to the cart rules with different numbers. 

 

7,000 for one item

7,000 for second item

7,000 for the third item

 

After this, the site is very slow after adding anything to the cart (it loads slowly and adding to cart or deleting an item from the cart is very slow).

 

I checked the database, and saw that the entries for these codes are located in 5 tables with 100,000 entries in those tables. Can this affect the site speed?

I using Prestashop 1.6.0.11

 

Here is the link to the site on a test domain so you can check how slow the site is loading - http://www2.sovkaz.com/

Edited by leonetrek (see edit history)
Link to comment
Share on other sites

I turned on Debug mode and saw that there were over 150 number of queries into the database, without the coupons. After I added coupons, the number of queries increased to over 80000. Why are there queries sent into the database if I simply added the coupons and that's it. The site is currently in debug mode and I left 3 coupons.  With each coupon I add, increases the number of queries 4 times. 

Link to comment
Share on other sites

hello

sadly prestashop check each cart rule that you have in your store

it's because of:

 

/classes/cartRule.php

	public static function autoAddToCart(Context $context = null)
	{
		if ($context === null)
			$context = Context::getContext();
		if (!CartRule::isFeatureActive() || !Validate::isLoadedObject($context->cart))
			return;

		$sql = '
		SELECT cr.*
		FROM '._DB_PREFIX_.'cart_rule cr
		LEFT JOIN '._DB_PREFIX_.'cart_rule_shop crs ON cr.id_cart_rule = crs.id_cart_rule
		'.(!$context->customer->id && Group::isFeatureActive() ? ' LEFT JOIN '._DB_PREFIX_.'cart_rule_group crg ON cr.id_cart_rule = crg.id_cart_rule' : '').'
		LEFT JOIN '._DB_PREFIX_.'cart_rule_carrier crca ON cr.id_cart_rule = crca.id_cart_rule
		'.($context->cart->id_carrier ? 'LEFT JOIN '._DB_PREFIX_.'carrier c ON (c.id_reference = crca.id_carrier AND c.deleted = 0)' : '').'
		LEFT JOIN '._DB_PREFIX_.'cart_rule_country crco ON cr.id_cart_rule = crco.id_cart_rule
		WHERE cr.active = 1
		AND cr.code = ""
		AND cr.quantity > 0
		AND cr.date_from < "'.date('Y-m-d H:i:s').'"
		AND cr.date_to > "'.date('Y-m-d H:i:s').'"
		AND (
			cr.id_customer = 0
			'.($context->customer->id ? 'OR cr.id_customer = '.(int)$context->cart->id_customer : '').'
		)
		AND (
			cr.`carrier_restriction` = 0
			'.($context->cart->id_carrier ? 'OR c.id_carrier = '.(int)$context->cart->id_carrier : '').'
		)
		AND (
			cr.`shop_restriction` = 0
			'.((Shop::isFeatureActive() && $context->shop->id) ? 'OR crs.id_shop = '.(int)$context->shop->id : '').'
		)
		AND (
			cr.`group_restriction` = 0
			'.($context->customer->id ? 'OR 0 < (
				SELECT cg.`id_group`
				FROM `'._DB_PREFIX_.'customer_group` cg
				INNER JOIN `'._DB_PREFIX_.'cart_rule_group` crg ON cg.id_group = crg.id_group
				WHERE cr.`id_cart_rule` = crg.`id_cart_rule`
				AND cg.`id_customer` = '.(int)$context->customer->id.'
				LIMIT 1
			)' : (Group::isFeatureActive() ? 'OR crg.`id_group` = '.(int)Configuration::get('PS_UNIDENTIFIED_GROUP') : '')).'
		)
		AND (
			cr.`reduction_product` <= 0
			OR cr.`reduction_product` IN (
				SELECT `id_product`
				FROM `'._DB_PREFIX_.'cart_product`
				WHERE `id_cart` = '.(int)$context->cart->id.'
			)
		)
		AND cr.id_cart_rule NOT IN (SELECT id_cart_rule FROM '._DB_PREFIX_.'cart_cart_rule WHERE id_cart = '.(int)$context->cart->id.')
		ORDER BY priority';
		$result = Db::getInstance()->executeS($sql);
		if ($result)
		{
			$cart_rules = ObjectModel::hydrateCollection('CartRule', $result);
			if ($cart_rules)
				foreach ($cart_rules as $cart_rule)
					if ($cart_rule->checkValidity($context, false, false))
						$context->cart->addCartRule($cart_rule->id);
		}
	}

can you for test purposes only, comment it out:

 

controllers/front/orderController.php, line ~302

			CartRule::autoAddToCart($this->context);

note: this is for test purposes only

Link to comment
Share on other sites

When I tried out what you suggested, the site started working faster. I added all the 5,000 discount codes but then it drastically slowed down again and the number of queries into the database didn't decrease.

 

SQL Queries30154 queries in 39235 ms - 5,000 discount codes

Edited by leonetrek (see edit history)
Link to comment
Share on other sites

I solved the issue and now the site works fast with 100,000 discount codes. I deleted the following on line 255 in /classes/cartRule.php:

		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM `'._DB_PREFIX_.'cart_rule` cr
		LEFT JOIN `'._DB_PREFIX_.'cart_rule_lang` crl ON (cr.`id_cart_rule` = crl.`id_cart_rule` AND crl.`id_lang` = '.(int)$id_lang.')
		WHERE (
			cr.`id_customer` = '.(int)$id_customer.' OR cr.group_restriction = 1
			'.($includeGeneric ? 'OR cr.`id_customer` = 0' : '').'
		)
		AND cr.date_from < "'.date('Y-m-d H:i:s').'"
		AND cr.date_to > "'.date('Y-m-d H:i:s').'"
		'.($active ? 'AND cr.`active` = 1' : '').'
		'.($inStock ? 'AND cr.`quantity` > 0' : ''));

If anyone knows, would this affect the site somehow?

Edited by leonetrek (see edit history)
Link to comment
Share on other sites

  • 1 year later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...