Reducing memory usage? I have a relatively simple task which is exposing an issue with efficiency. In that it isn't very efficient and the server cries and quits. Here's the high level overview:

There's a category called "Locations". There are 791 locations.
Users have three Category Fields; First Choice, Second Choice, Third Choice. The pick one location in each field. There are 1809 Users.

I need to generate a report that shows how popular each location is (how many Users chose [LOCATION] as their 1st, 2nd, and 3rd choice). A nice simple table that's 4 cells wide and 791 rows deep.

I keep running out of memory and execution time regardless of how high I take each limit. I've tried it in Twig and I've tried it in PHP as a plugin.

My strategy, via creating a Service and a Variable... :

{% set locations = craft.categories.group('locations').limit(null) %}
{% set users     = craft.users.group('members').limit(null) %}

{{ craft.reporting.locationPopularity(locations,users) }}


The ReportsService.php file:

public function locationPopularity($locations = null, $users = null) {
    $response = "";

    foreach($locations as $location) {
        $firstChoiceCount = 0;
        $secondChoiceCount = 0;
        $thirdChoiceCount = 0;

        foreach($users as $user) {
            if($user-&gt;firstChoiceLocation-&gt;last() == $location){ $firstChoiceCount += 1; }
            if($user-&gt;secondChoiceLocation-&gt;last() == $location){ $secondChoiceCount += 1; }
            if($user-&gt;thirdChoiceLocation-&gt;last() == $location){ $thirdChoiceCount += 1; }
        }

        $response = $response . "
            &lt;h2&gt;$location-&gt;title&lt;/h2&gt;
            &lt;p&gt;First Choice: $firstChoiceCount, Second Choice: $secondChoiceCount, Third Choice: $thirdChoiceCount.&lt;/p&gt;";
    }

    return $response;
}


How do I get this to work? 1,430,919 loop iterations seems to be a no go.

The following works, but 2400+ queries...

{% for location in craft.categories.group('locations').limit(null).find() %}
    {% set firstChoiceCount = craft.users.relatedTo({
        element: location,
        field: 'firstChoiceLocation'
    }).limit(null).count() %}

    {% set secondChoiceCount = craft.users.relatedTo({
        element: location,
        field: 'secondChoiceLocation'
    }).limit(null).count() %}

    {% set thirdChoiceCount = craft.users.relatedTo({
        element: location,
        field: 'thirdChoiceLocation'
    }).limit(null).count() %}

    ... output ...
{% endfor %}
First of all -&gt;last() will always produce 2 queries, one fetches the total amount of results, the other one fetches the result with the offset given by the first query -> you can reduce the amount of queries by using first() if there is only one choice.

Furthermore: I would make use of eager loading so you don't even fetch the fields again and again (like currently)

{% set users = craft
    .users
    .group('members')
    .limit(null)
    .with([
        'firstChoiceLocation', 
        'secondChoiceLocation', 
        'thirdChoiceLocation'
    ])
    .find() 
%}


Besides that - if you don't execute your $users query Craft might fetch the results again and again in certain circumstances so you don't have an array of elements but a query that is fired every single time

In the end I would highly suggest to not loop each user again and again, instead fetch the number of related users for each category via custom Query.
You can just count relations.targetId from the relations table where the sourceIds are your category ids and loop the result within PHP. I could show it for Craft 3 but I didn't use Craft 2 syntax for over a year