Answers.Body: <p>Your n+1 problem comes from accessing <code>port.parent</code> in the Ports menu loop. That is being called for each category, and each time it is triggering a new element query.</p>
  
  <p>The right way to go about this is to load only the top-level categories (the Countries) using the <code>level=1</code> param, <a href="https://craftcms.com/docs/templating/eager-loading-elements" rel="nofollow">eager-loaded</a> with their children (the Ports):</p>
  
  <pre><code>{% set countries = craft.categories({
      group: 'locations',
      level: 1,
      limit: null,
      with: ['children']
  }) %}
  </code></pre>
  
  <p>You can then build the Countries menu without worrying about which level the categories have:</p>
  
  <pre><code>&lt;label&gt;Country&lt;/label&gt;
  &lt;select id="countries" class="countries" name="country"&gt;
      &lt;option value=""&gt;Select...&lt;/option&gt;
  
      {% for country in countries %}
          &lt;option value="{{ country.slug }}"&gt;{{ country.title }}&lt;/option&gt;
      {% endfor %}
  &lt;/select&gt;
  </code></pre>
  
  <p>And to build the Ports menu, just loop over the countries again, and for each one, loop through its eager-loaded children. Set each port option’s <code>class</code> attribute to the current country’s slug (since that’s what you’re setting the country menu options’ values to).</p>
  
  <pre><code>&lt;label&gt;Ports&lt;/label&gt;
  &lt;select id="ports" name="port" class="ports" disabled="disabled"&gt;
      &lt;option value=""&gt;Select a country first&lt;/option&gt;
  
      {% for country in countries %}
          {% for port in country.children %}
              &lt;option value="{{ port.slug }}" class="{{ country.slug }}"&gt;{{ port.title }}&lt;/option&gt;
          {% endfor %}
      {% endfor %}
  &lt;/select&gt;
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-18T17:56:15.230Z
Answers.Text: Brandon! thank you very much. The loop now loads in about 1.5 seconds. With some more optimisation with caching tags in the right place the whole google map directory clustering pagination search form is coming in at 0.929 - 1.3secs 
  Can you see my stupid fat grin :))))) Thank you.
Answers.UserDisplayName: joomkit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-19T15:20:07.530Z
Answers.Text: Not sure if this was just a hardware lull but Im back to 3.5secs. Nothing i can do gets this down. Why cant i just load categories without all the joins to unnecessary elements?
Answers.UserDisplayName: joomkit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-21T16:42:02.980Z
Answers.Text: One more thought. Surely this is an argument for just being able to access the categories without elements and without locales? Some excluding criteria? This would cut down on those expensive joins in the first place ?
Answers.UserDisplayName: joomkit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-18T13:27:48.887Z
Answers.LastActivityDate: 2016-10-18T13:27:48.887Z
Answers.UserDisplayName: Brandon Kelly
Answers.Body: <p>It's hard to know for sure without seeing your query profile output, but try eager loading your <code>ports</code> field like so:</p>
  
  <pre><code>{% set categories = craft.categories({
      group: 'locations',
      limit: null,
      with: ['ports']
  }) %}
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-17T09:15:01.933Z
Answers.Text: This seems like a thorny problem that cant be solved by eager loading. It looks like every time i loop thru the categories to build my chained select, crafts db call is trying to pull back the element content that is assigned to the category. This is not what i want - i just want the categories and nothing else. After all I am building a search form - i dont need the content yet. So it looks like Craft assumes because I want a category i want the associated Entries too. Is this what Craft is doing?
  If so the problem could be redefined as how do i exclude entries from my categories request
Answers.UserDisplayName: joomkit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-18T10:52:03.830Z
Answers.Text: Any more on this Brad?
Answers.UserDisplayName: joomkit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-18T11:40:09.237Z
Answers.Text: I don’t think Ports are coming from a relation field – looks like they are just nested categories in the same category group as countries.
Answers.UserDisplayName: Brandon Kelly
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-18T11:59:57.900Z
Answers.Text: Yep thats right. But calling the categories to populate two drop downs for search is costing 5seconds of db action. Its the running thru the loop to populate the options that seems to cause the load time. I cant see a way around it :(
Answers.UserDisplayName: joomkit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-10-16T16:49:15.650Z
Answers.LastActivityDate: 2016-10-16T16:49:15.650Z
Answers.UserDisplayName: Brad Bell
Body: <p>I have a directory of sea ports with a google map that loads about 450 records. The map and listings and pagination work fine. On their own, I get a 2.5 sec page load average.</p>
  
  <p>However as soon as I add a search form that populates a chained select for countries and ports the load time goes up to 8 secs.</p>
  
  <p>The search form code looks like this </p>
  
  <pre><code>{% set categories = craft.categories.group('locations').limit(null) %}
  
  &lt;label&gt;Country&lt;/label&gt;
  &lt;select id="countries" class="countries" name="country"&gt;
      &lt;option value=""&gt;Select...&lt;/option&gt;
  
      {% cache %}
          {% for country in categories %}
              {% if country.level == 1 %}
                  &lt;option value="{{ country.slug }}"&gt;{{ country.title }}&lt;/option&gt;
              {% endif %}
          {% endfor %}
      {% endcache %}
  &lt;/select&gt;
  
  &lt;label&gt;Ports&lt;/label&gt;
  &lt;select id="ports" name="port" class="ports" disabled="disabled"&gt;
      &lt;option value=""&gt;Select a country first&lt;/option&gt;
  
      {% cache %}
          {% for port in categories %}
              &lt;option value="{{ port.slug }}" class="{{ port.parent | lower }}"&gt;{{ port.title }}&lt;/option&gt;
         {% endfor %}
      {% endcache %}
  
  &lt;/select&gt;
  </code></pre>
  
  <p>It seems that this code cost about 5 seconds of load time.</p>
  
  <p>I have checked the profile output and seen the query for the categories. It's huge.  I can't see how to get the page to load faster as I can't see eager loading applying to a category group, and caching doesn't really help here.</p>
  
  <p>Any ideas on how to optimise this portion?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-10-14T19:52:57.797Z
Comments.Text: Seems like the N+1 problem that eager loading is meant to solve.  Why not eager load `ports` in your initial `categories` query? https://craftcms.com/docs/templating/eager-loading-elements
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-10-16T14:48:48.530Z
Comments.Text: Can you eager load a categories? Not sure what this looks like. Is it the nesting of the categories that equals the n+1 problem. I was surprised at the query length in profile. It's only about 80 level 1 categories and a few hundred level2 . It's really disappointing I didn't optimise at the outset as it's a bit of a blocker.
Comments.UserDisplayName: joomkit
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-10-18T11:45:21.953Z
Comments.Text: To clarify, it sounds like both Countries and Ports are defined in the same category group (Countries at the top level; Ports nested under them) – is that correct? I see that you are only displaying top-level categories in the Countries menu (`{% if country.level == 1 %}`), but I’m not seeing anything similar for the Ports menu. How are you limiting the Ports menu to the second-level categories?
Comments.UserDisplayName: Brandon Kelly
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-10-18T12:51:15.053Z
Comments.Text: Ports is the same array loop as country categories but echoes level 2 -code above not quite right (slaps self) them users j query to chain the selects. Even building just one select seems to take 3secs. Is it getting all the content elementsts in the categories too?
Comments.UserDisplayName: joomkit
Tags: <cache><performance>
Title: Category query very slow on multilingual site
ThreadText: # Category query very slow on multilingual site
  
  (Asked by: username_0 on 2016-10-14)
  
  I have a directory of sea ports with a google map that loads about 450 records. The map and listings and pagination work fine. On their own, I get a 2.5 sec page load average.
  
  However as soon as I add a search form that populates a chained select for countries and ports the load time goes up to 8 secs.
  
  The search form code looks like this
  
  ```
  {% set categories = craft.categories.group('locations').limit(null) %}
  
  <label>Country</label>
  <select id="countries" class="countries" name="country">
      <option value="">Select...</option>
  
      {% cache %}
          {% for country in categories %}
              {% if country.level == 1 %}
                  <option value="{{ country.slug }}">{{ country.title }}</option>
              {% endif %}
          {% endfor %}
      {% endcache %}
  </select>
  
  <label>Ports</label>
  <select id="ports" name="port" class="ports" disabled="disabled">
      <option value="">Select a country first</option>
  
      {% cache %}
          {% for port in categories %}
              <option value="{{ port.slug }}" class="{{ port.parent | lower }}">{{ port.title }}</option>
         {% endfor %}
      {% endcache %}
  
  </select>
  
  ```
  
  It seems that this code cost about 5 seconds of load time.
  
  I have checked the profile output and seen the query for the categories. It's huge. I can't see how to get the page to load faster as I can't see eager loading applying to a category group, and caching doesn't really help here.
  
  Any ideas on how to optimise this portion?
  
  **Tags:** cache, performance
  
  > **Comments:**
  > **username_1**: Seems like the N+1 problem that eager loading is meant to solve.  Why not eager load `ports` in your initial `categories` query? https://craftcms.com/docs/templating/eager-loading-elements  
  > **username_0**: Can you eager load a categories? Not sure what this looks like. Is it the nesting of the categories that equals the n+1 problem. I was surprised at the query length in profile. It's only about 80 level 1 categories and a few hundred level2 . It's really disappointing I didn't optimise at the outset as it's a bit of a blocker.  
  > **username_2**: To clarify, it sounds like both Countries and Ports are defined in the same category group (Countries at the top level; Ports nested under them) – is that correct? I see that you are only displaying top-level categories in the Countries menu (`{% if country.level == 1 %}`), but I’m not seeing anything similar for the Ports menu. How are you limiting the Ports menu to the second-level categories?  
  > **username_0**: Ports is the same array loop as country categories but echoes level 2 -code above not quite right (slaps self) them users j query to chain the selects. Even building just one select seems to take 3secs. Is it getting all the content elementsts in the categories too?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2016-10-18. Score: 1 | Accepted answer
  
  Your n+1 problem comes from accessing `port.parent` in the Ports menu loop. That is being called for each category, and each time it is triggering a new element query.
  
  The right way to go about this is to load only the top-level categories (the Countries) using the `level=1` param, [eager-loaded](https://craftcms.com/docs/templating/eager-loading-elements) with their children (the Ports):
  
  ```
  {% set countries = craft.categories({
      group: 'locations',
      level: 1,
      limit: null,
      with: ['children']
  }) %}
  
  ```
  
  You can then build the Countries menu without worrying about which level the categories have:
  
  ```
  <label>Country</label>
  <select id="countries" class="countries" name="country">
      <option value="">Select...</option>
  
      {% for country in countries %}
          <option value="{{ country.slug }}">{{ country.title }}</option>
      {% endfor %}
  </select>
  
  ```
  
  And to build the Ports menu, just loop over the countries again, and for each one, loop through its eager-loaded children. Set each port option’s `class` attribute to the current country’s slug (since that’s what you’re setting the country menu options’ values to).
  
  ```
  <label>Ports</label>
  <select id="ports" name="port" class="ports" disabled="disabled">
      <option value="">Select a country first</option>
  
      {% for country in countries %}
          {% for port in country.children %}
              <option value="{{ port.slug }}" class="{{ country.slug }}">{{ port.title }}</option>
          {% endfor %}
      {% endfor %}
  </select>
  
  ```
  
  > **Comments:**
  > **username_0**: username_2! thank you very much. The loop now loads in about 1.5 seconds. With some more optimisation with caching tags in the right place the whole google map directory clustering pagination search form is coming in at 0.929 - 1.3secs  Can you see my stupid fat grin :))))) Thank you.  
  > **username_0**: Not sure if this was just a hardware lull but Im back to 3.5secs. Nothing i can do gets this down. Why cant i just load categories without all the joins to unnecessary elements?  
  > **username_0**: One more thought. Surely this is an argument for just being able to access the categories without elements and without locales? Some excluding criteria? This would cut down on those expensive joins in the first place ?  
  
  ---
  
  ### Answer by username_1 on 2016-10-16. Score: 1
  
  It's hard to know for sure without seeing your query profile output, but try eager loading your `ports` field like so:
  
  ```
  {% set categories = craft.categories({
      group: 'locations',
      limit: null,
      with: ['ports']
  }) %}
  
  ```
  
  > **Comments:**
  > **username_0**: This seems like a thorny problem that cant be solved by eager loading. It looks like every time i loop thru the categories to build my chained select, crafts db call is trying to pull back the element content that is assigned to the category. This is not what i want - i just want the categories and nothing else. After all I am building a search form - i dont need the content yet. So it looks like Craft assumes because I want a category i want the associated Entries too. Is this what Craft is doing? If so the problem could be redefined as how do i exclude entries from my categories request  
  > **username_0**: Any more on this username_1?  
  > **username_2**: I don’t think Ports are coming from a relation field – looks like they are just nested categories in the same category group as countries.  
  > **username_0**: Yep thats right. But calling the categories to populate two drop downs for search is costing 5seconds of db action. Its the running thru the loop to populate the options that seems to cause the load time. I cant see a way around it :(  
  
  ---
  
  