1

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?

  • Seems like the N+1 problem that eager loading is meant to solve. Why not eager load ports in your initial categories query? craftcms.com/docs/templating/eager-loading-elements – Brad Bell Oct 14 '16 at 19:52
  • 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 Oct 16 '16 at 14:48
  • 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 Oct 18 '16 at 11:45
  • 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 Oct 18 '16 at 12:51
1

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:

<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>
|improve this answer|||||
  • 1
    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 Oct 18 '16 at 17:56
  • 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 Oct 19 '16 at 15:20
  • 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 Oct 21 '16 at 16:42
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']
}) %}
|improve this answer|||||
  • 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 Oct 17 '16 at 9:15
  • Any more on this Brad? – joomkit Oct 18 '16 at 10:52
  • 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 Oct 18 '16 at 11:40
  • 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 Oct 18 '16 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.