[5.10.1]: Major query efficiency/eager loading problem

[BramMortier]

What happened?

Description

I'm in the process of building a big content site that has a lot of content blocks. So during development I made sure to optimize my queries and always watch the query count in the Yii toolbar. I needed around 120 queries for a big page before the upgrade. Now I'm seeing 400+ queries easily.

I've done some looking and found this example in my primary navigation component:

It's a pretty deeply nested navigation with multiple levels of submenu's. Perfect use case for eager loading. Removing the eagerly() statements here reduces my queries needed by 140? Was anything changes to the way this works?

{# ======================= #}
{# PROPERTIES              #}
{# ======================= #}
{% set primaryNavigation = header.primaryNavigationField.eagerly().all() %}

{# ======================= #}
{# TEMPLATE                #}
{# ======================= #}
<nav class="c-primary-navigation">
    <ul class="c-primary-navigation-link-list">
        {% for link in primaryNavigation %}
            <li class="c-primary-navigation-link-list-item">
                {% if link.type == "navigationLink" %}

                    {% include "_components/navigationLink" with { link } only %}

                {% elseif link.type == "linkMenu" %}

                    {% set links = link.navigationLinksField.all() %}

                    {% include "_components/navigationLink" with { link, icon: "bubble-chevron-down" } only %}

                    <ul class="c-primary-navigation-menu-link-list">
                        {% for link in links %}
                            <li class="c-primary-navigation-menu-link-list-item">
                                {% include "_components/navigationLink" with { link } only %}
                            </li>
                        {% endfor %}
                    </ul>
                
                {% elseif link.type == "linkAdvancedMenu" %}

                    {% set linkGroups = link.linkGroupsField.eagerly().all() %}

                    {% include "_components/navigationLink" with { link, icon: "bubble-chevron-down" } only %}

                    <ul class="c-primary-navigation-advanced-menu-group-list">
                        {% for group in linkGroups %}
                            {% set links = group.navigationLinksField.eagerly().all() %}

                            <li class="c-primary-navigation-advanced-menu-group-list-item">
                                {% include "_components/navigationLink" with { link: group, icon: "bubble-chevron-right" } only %}

                                <ul class="c-primary-navigation-group-links-list">
                                    {% for link in links %}
                                        <li class="c-primary-navigation-group-links-list-item">
                                            {% include "_components/navigationLink" with { link } only %}
                                        </li>
                                    {% endfor %}
                                </ul>
                            </li>
                        {% endfor %}
                    </ul>
                    
                {% endif %}
            </li>
        {% endfor %}
    </ul>
</nav>

Steps to reproduce

Expected behavior

Actual behavior

Craft CMS version

5.10.1

PHP version

No response

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[linear-code]

CMS-2112

[BramMortier]

I've tested this with multiple other sites in the meantime and noticed all of them are less efficient. Not as drastic as this one maybe 20 or 30 extra queries. But they didn't have the deeply nested structure.

[brandonkelly]

Any chance you can send your database backup and Composer files into support@craftcms.com?

[BramMortier]

@brandonkelly Should be in your inbox!

[BramMortier]

More context from the Yii debug bar 138 duplicate queries with some being executed 15 times

[brandonkelly]

Thanks! I was able to reproduce, and got this fixed for the next release.

You can get the fix early by changing your craftcms/cms requirement in composer.json to:

"craftcms/cms": "5.x-dev as 5.10.2",

Then run composer update.

[brandonkelly]

Craft 5.10.3 is out with that fix.