<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>

CC BY-SA 3.0
2016-10-18T17:56:15.230Z
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.
joomkit
CC BY-SA 3.0
2016-10-19T15:20:07.530Z
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?
joomkit
CC BY-SA 3.0
2016-10-21T16:42:02.980Z
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 ?
joomkit
CC BY-SA 3.0
2016-10-18T13:27:48.887Z
2016-10-18T13:27:48.887Z
Brandon Kelly
<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>

CC BY-SA 3.0
2016-10-17T09:15:01.933Z
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
joomkit
CC BY-SA 3.0
2016-10-18T10:52:03.830Z
Any more on this Brad?
joomkit
CC BY-SA 3.0
2016-10-18T11:40:09.237Z
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.
Brandon Kelly
CC BY-SA 3.0
2016-10-18T11:59:57.900Z
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 :(
joomkit
CC BY-SA 3.0
2016-10-16T16:49:15.650Z
2016-10-16T16:49:15.650Z
Brad Bell
<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>

CC BY-SA 3.0
2016-10-14T19:52:57.797Z
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
Brad Bell
CC BY-SA 3.0
2016-10-16T14:48:48.530Z
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.
joomkit
CC BY-SA 3.0
2016-10-18T11:45:21.953Z
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?
Brandon Kelly
CC BY-SA 3.0
2016-10-18T12:51:15.053Z
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?
joomkit
<cache><performance>
Category query very slow on multilingual site
# 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) %}

Country

    Select...

    {% cache %}
        {% for country in categories %}
            {% if country.level == 1 %}
                {{ country.title }}
            {% endif %}
        {% endfor %}
    {% endcache %}


Ports

    Select a country first

    {% cache %}
        {% for port in categories %}
            {{ port.title }}
       {% endfor %}
    {% endcache %}



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 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:
Country

    Select...

    {% for country in countries %}
        {{ country.title }}
    {% endfor %}


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).
Ports

    Select a country first

    {% for country in countries %}
        {% for port in country.children %}
            {{ port.title }}
        {% endfor %}
    {% endfor %}

> **Comments:**
> **username_0**: 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.  
> **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 Brad?  
> **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 :(  

---

