DB Queries in Craft Commerce 2 with large data
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!