SOQL aggregate maxing out SOQL limits

Question

I'm having trouble with my SOQL, running into SOQL limits in production. My SOQL is counting all active Users grouped by their Freeze status. How do I re-write the following SOQL without a subquery. Instead of a subquery I tried User.IsActive = true. That didn't work.

  SELECT count(Id) CountOfUsers,  IsFrozen FreezeStatus, max(LastModifiedDate) LastModified FROM UserLogin WHERE UserId IN 
                          (SELECT Id FROM User WHERE IsActive = true) group by IsFrozen.

Looks like the subquery is causing the max out eventhough I'm returning an aggregate of 2 rows.

Answer 1

The rows from your Left Inner Join do not count against your governors. If you're running into row limits, it is strictly because you have too many UserLogin records.

If you want to know the number of User records, query on User. You can use the same type of join, just in the other direction.

Integer frozenUserCount = [
    SELECT count() FROM User WHERE Id IN (
        SELECT UserId FROM UserLogin WHERE IsFrozen = true
    )
];
Datetime frozenLastModified = [
    SELECT LastModifiedDate
    FROM UserLogin
    WHERE IsFrozen = true
    ORDER BY LastModifiedDate DESC
    LIMIT 1
].LastModifiedDate;

Integer notFrozenUserCount = [
    SELECT count() FROM User WHERE Id IN (
        SELECT UserId FROM UserLogin WHERE IsFrozen = false
    )
];
Datetime notFrozenLastModified = [
    SELECT LastModifiedDate
    FROM UserLogin
    WHERE IsFrozen = false
    ORDER BY LastModifiedDate DESC
    LIMIT 1
].LastModifiedDate;