DB Queries in Craft Commerce 2 with large data Currently working on an e-commerce site, which currently has over 4000 products with a mass of variants (averaging 3 variants per product) - and hit a few hurdles on the way...

At present, craft.products.hasVariant({hasSales: true}) takes over 4 minutes to return the data of items on sale. And again running a product count against the brand (category) is a similar issue.

Now I understand the sheer amount of queries for the above is getting a little ridiculous around 300 are showing in the Logs each time this is run - so running these queries from twig is next to impossible with the overheads.

So now turned to use a plugin with a public function in order to try and ease the pressure from the front end...

Anyone any ideas on how get the same results with a craft\db\Query...

Thanks in advance...

I hope this makes sense...
Okay for anyone who runs into issues with Craft Commerce and has large amounts of products (over 4K) with 8K's worth of variants...

You'll need to create a plugin and execute a function in the variables.php like so...

public function getClearanceProductIds() {
      $rows = (new \craft\db\Query())
      -&gt;select('craft_commerce_variants.productId')
      -&gt;from('craft_commerce_sale_purchasables')
      -&gt;join('JOIN','craft_commerce_variants','craft_commerce_variants.id = craft_commerce_sale_purchasables.purchasableId')
      -&gt;where(array(
        'like',
        'craft_commerce_sale_purchasables.saleId',
        '2'
      ))
      -&gt;column();

      $rows = implode(",", $rows);

      return $rows;

    }


And then query the products in the normal way with the ids array

{% set products   = craft.products.id(saleItems).all() %}


Otherwise, every your resources and CPU will overload by doing things the craft way i.e.

{% set products = craft.products.hasVariant({hasSales: true}).all() %}


I hope this helps someone with a similar issue!