"OrgCheck is spinning" but ending with error

[arijitdatta-sf]

For a specific org, while using Org Check 6.4, we observed that the Internal Role Explorer check ran for almost 20 minutes before ending with this error.
Upon further investigation, we noticed that the same issue occurs for other options wherever it starts with “OrgCheck is spinning…”

On the console, the following is being shown

[VinceFINET]

Thanks for the chat we had today.
So the situation of your org is that you have a lot of users with a lot of permission sets assigned to them.

Some stats:

• 19 internal user roles (clearly not the issue here)
• 180k active internal users (if we get the users alone the query will be much faster like batch size=2k)
• each users has like 40 to 200 permission sets assigned (so getting the users and the permsetlist at the same time reduce the velocity of the user query globally!!)

[VinceFINET]

The current unique SOQL in the active internal users dataset is

SELECT Id, Name, ProfileId, LastLoginDate, LastPasswordChangeDate, NumberOfFailedLogins, UserPreferencesLightningExperiencePreferred, (SELECT PermissionSetId FROM PermissionSetAssignments WHERE PermissionSet.IsOwnedByProfile = false)
FROM User 
WHERE IsActive = true 
AND ContactId = NULL 
AND Profile.Id != NULL

And in your case it takes ages because the batch size is very small (it is calculated by salesforce automatically).
It it small because a lot of sub records (the perm set assignments)

[VinceFINET]

If we split the first SOQL into two:

SOQL #1

SELECT Id, Name, ProfileId, LastLoginDate, LastPasswordChangeDate, NumberOfFailedLogins, UserPreferencesLightningExperiencePreferred
FROM User 
WHERE IsActive = true 
AND ContactId = NULL 
AND Profile.Id != NULL

Then SOQL #2

SELECT AssigneeId, PermissionSetId 
FROM PermissionSetAssignment 
WHERE Assignee.IsActive = true
AND PermissionSet.IsOwnedByProfile = false
AND Assignee.ContactId = NULL 
AND Assignee.Profile.Id != NULL

When the SOQL 1 is way better (2k by 2k) the SOQL 2 remains very slow

[VinceFINET]

We could have SOQL 2 run with custom query more to make it faster