Body: <p>We have 8000 products imported in the database, with a few 100 categories, sometimes up to 4 levels deep. </p>
  
  <p>What we try to do is that when a user clicks a main category, we show 4 random products of each subcategory (but only for it's direct children). </p>
  
  <p>This is about 10 subcategories, that are directly under each main category. Though I imagine the <code>relatedTo</code> digs as many levels deep as it can.</p>
  
  <p>The code works, but it takes 15 seconds before the page is loaded, so this is not workable.</p>
  
  <p>I'm quite sure it might be the <code>relatedTo</code> tag that slows it down. 
  Plus it also seems that when using <code>relatedTo</code> and a <code>limit</code> parameter together, that limit just gets ignored, and it seems he's grabbing all records?</p>
  
  <p>Here's the code: </p>
  
  <pre><code>{% set categories = craft.categories.group('pharmacyCategories').descendantOf(category.id).level(2).order('catDisplayName') %}
  
  {% for subcat in categories %}
  
      {% set productsSubCategory = craft.commerce.products.relatedTo(subcat)  %}
  
      &lt;h2&gt;&lt;a href="/producten/{{ category.slug }}/{{ subcat.slug }}"&gt;{{ subcat.catDisplayName }} ({{ productsSubCategory.total }})&lt;/a&gt;&lt;/h2&gt;
  
      {% for product in productsSubCategory.limit(4).order('RAND()') %}
          {% if product.variants|length %}
                &lt;a class="title" href="/product/{{ product.slug }}"&gt;{{ product.title }}&lt;/a&gt;
          {% endif %}
      {% endfor %}
  
  {% endfor %}
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-11T18:14:32.203Z
Comments.Text: Tom did you ever get a solution, surprised this never got an answer.  I have a similar issue currently.
Comments.UserDisplayName: Vin
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-11T22:31:32.927Z
Comments.Text: Hi Vin, no, we were not able to fix these performance problems. We reluctantly looked for alternatives, and went for Prestashop. It looks like a 2003 php project, though has a huge community and many plugins that CraftCommerce doesn't have. 
  
  For example: postoffice integration, so the clients only have to print out the labels. 
  
  We'ld love to move everything to CraftCommerce eventually, but it will need a little bit more time to mature.
Comments.UserDisplayName: tom
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-11T22:32:58.190Z
Comments.Text: Thanks for letting me know Tom, at least I am glad you found an alternative.  When I dig out from my own issues I will share.
Comments.UserDisplayName: Vin
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-11T22:39:40.750Z
Comments.Text: Also, we were on a deadline, and prestashop offers alot more out of the box.  Even so: the end product works excellent and once if you 'recognise" Presta, you'll notice how many shops are using it. 
  
  My advice would be: if you're not in a hurry, and depending on your client's customisations (if there's not that many), stick with Craft. If you want to speed up progress, and don't mind messing around in php stuff, give Presta a try, it's much more feature build.  Hope this helps and would be nice if you keep me posted. As obviously I'd like to stay to the P&T ecosystem ideally eventually too.
Comments.UserDisplayName: tom
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-11T22:57:02.520Z
Comments.Text: Awesome, thanks for the advice, I agree.  I'm going to try and hangout with Craft as long as possible with this one.  It's a personal project, so that affords me the luxury of time :)
Comments.UserDisplayName: Vin
Tags: <plugin-craftcommerce><categories><relatedto>
Title: Showing products from many categories becomes very slow
ThreadText: # Showing products from many categories becomes very slow
  
  (Asked by: username_0 on 2017-07-27)
  
  We have 8000 products imported in the database, with a few 100 categories, sometimes up to 4 levels deep.
  
  What we try to do is that when a user clicks a main category, we show 4 random products of each subcategory (but only for it's direct children).
  
  This is about 10 subcategories, that are directly under each main category. Though I imagine the `relatedTo` digs as many levels deep as it can.
  
  The code works, but it takes 15 seconds before the page is loaded, so this is not workable.
  
  I'm quite sure it might be the `relatedTo` tag that slows it down.
  Plus it also seems that when using `relatedTo` and a `limit` parameter together, that limit just gets ignored, and it seems he's grabbing all records?
  
  Here's the code:
  
  ```
  {% set categories = craft.categories.group('pharmacyCategories').descendantOf(category.id).level(2).order('catDisplayName') %}
  
  {% for subcat in categories %}
  
      {% set productsSubCategory = craft.commerce.products.relatedTo(subcat)  %}
  
      <h2><a href="/producten/{{ category.slug }}/{{ subcat.slug }}">{{ subcat.catDisplayName }} ({{ productsSubCategory.total }})</a></h2>
  
      {% for product in productsSubCategory.limit(4).order('RAND()') %}
          {% if product.variants|length %}
                <a class="title" href="/product/{{ product.slug }}">{{ product.title }}</a>
          {% endif %}
      {% endfor %}
  
  {% endfor %}
  
  ```
  
  **Tags:** plugin-craftcommerce, categories, relatedto
  
  > **Comments:**
  > **username_1**: Tom did you ever get a solution, surprised this never got an answer.  I have a similar issue currently.  
  > **username_0**: Hi Vin, no, we were not able to fix these performance problems. We reluctantly looked for alternatives, and went for Prestashop. It looks like a 2003 php project, though has a huge community and many plugins that CraftCommerce doesn't have.   For example: postoffice integration, so the clients only have to print out the labels.   We'ld love to move everything to CraftCommerce eventually, but it will need a little bit more time to mature.  
  > **username_1**: Thanks for letting me know Tom, at least I am glad you found an alternative.  When I dig out from my own issues I will share.  
  > **username_0**: Also, we were on a deadline, and prestashop offers alot more out of the box.  Even so: the end product works excellent and once if you 'recognise" Presta, you'll notice how many shops are using it.   My advice would be: if you're not in a hurry, and depending on your client's customisations (if there's not that many), stick with Craft. If you want to speed up progress, and don't mind messing around in php stuff, give Presta a try, it's much more feature build.  Hope this helps and would be nice if you keep me posted. As obviously I'd like to stay to the P&T ecosystem ideally eventually too.  
  > **username_1**: Awesome, thanks for the advice, I agree.  I'm going to try and hangout with Craft as long as possible with this one.  It's a personal project, so that affords me the luxury of time :)  
  
  ---
  
  ## 0 answers
  
  