Massive query spikes and significantly slower page load times in certain circumstances

[benjaminkohl]

Craft: 3.3.13
Relations: 1.2.1

We were recently having trouble optimizing one of our sites due to massive query spikes during page loads that caused very slow load times. We tracked it down to a number of Twig and PHP syntax choices that were avoidable but it kept bothering us that seemingly innocent bits of logic would cause these massive query spikes. I started running some tests in some very simple templates and I tracked it down to elements with field layouts that contained the Relations field. In my local environment, I deleted the field and the query spikes went away. But here are a few examples that affected performance:

144 queries

{% set oneCat = craft.categories.id(52032).one() %}
{% if oneCat %}
	<h1>{{ oneCat.title|e }}</h1>
{% endif %}

2,000+ queries

{% set oneCat = craft.categories.id(52032).one() %}
{% if oneCat is not empty %}
	<h1>{{ oneCat.title|e }}</h1>
{% endif %}

A PHP loop from a Yii/Craft module

<?php
foreach($category as $cat) {
    // This loop could be empty. Simply iterating over the array of elements executed thousands upon thousands of queries.
}

Sometimes we had template partials that could be used by a number of entry types and some of those entry types didn't have a particular relationship field so we would check for it using the Twig length filter. Field layouts lacking the relationship field would have a null value in that attribute. This check also caused massive query spikes in cases.

{# Massive query increase, slower page load. #}
{% set relatedEntries = entry.relatedEntriesField|length ? entry.relatedEntriesField : [] %}

{# No increase in queries. Page loads quickly. #}
{% set relatedEntries = entry.relatedEntriesField is not empty ? entry.relatedEntriesField : [] %}
{% set relatedEntries = entry.relatedEntriesField ? entry.relatedEntriesField : [] %}

My theory is that certain Twig operations cause Craft elements to execute their fields' normalizeValue() methods and I see this field type executes the queries when that method is called.

[ul8]

For me this slowed a simple action such as

$entry = new \craft\elements\Entry();
$entry->sectionId = 3;
$entry->typeId = 4;
Craft::$app->elements->saveElement($entry)

to take almost 2 minutes in local dev mode, writing tons of logs. It's a large DB with a few thousand entries, however the sections used in the relations field only have a few hundred.