5

I have a requirement to change the status of any Contact (master object) that has had a Booking (detail object) but not in the last 2 years to "Inactive":

Date d = Date.today().addYears(-2);
Contact[] contacts = [
        select Id
        from Contact
        where Status__c != 'Inactive'
        and Id in (select Client__c from Booking__c)
        and Id not in (select Client__c from Booking__c where LatestBookedDate__c > :d)
        ];

The org will have about 2,000 Contact objects but will end up with more than 50,000 Booking objects. In the content of the above code, I am wondering how this governor limit applies:

Total number of records retrieved by SOQL queries 50,000

I assume/hope it only applies to the Contact object returned by the query and not to the sub-select Booking queries. (But perhaps SOQL aggregate query limit implies the opposite.) Please clarify if you know.

(I'm not looking for alternative queries here, I'm looking to understand how the governor limit is applied.)

5

According to my knowledge Limit of 50k objects applies to each object query returns. So each element is counted as 1- no matter from query or nested query. Although list you used as filters (semi-join)are not returning any data and are not count against this limit.

There is no limit on the number of records matched in a subquery. 
Standard SOQL query limits apply to the main query.
4

No

An Inner join / semi-join does not count against the records retrieved limit of 50,000 records. However, there is not much (if any) documentation supporting this claim. But, since we're coding, we can test this and see proof below. Note: even though an inner join / semi-join doesn't count against Limits.getQueryRows(), it can impact query performance.

Here's a proof:

final Account account = new Account(
    Name = 'Test Account'
);
{
    insert account;
}   

final Contact[] contacts = new Contact[] {};
for(Integer i = 0; i < 200; i ++) {
    contacts.add(new Contact(
        LastName = 'Last-' + i, 
        AccountId = account.Id
    ));
}
{
    insert contacts;
}   

// Test 1: Query Accounts
{
    // Initial condition
    {
        System.assertEquals(0, Limits.getQueryRows());
    }   

    final Integer expected = 1; 

    final Account[] accounts = [
        SELECT Id
        FROM Account
        WHERE Id IN (
            SELECT AccountId
            FROM Contact
            WHERE Id IN :contacts
        )
    ];  

    final Integer actual = Limits.getQueryRows();  

    // Assert results are valid
    {
        System.assertEquals(1, accounts.size());
        System.assertEquals(account.Id, accounts[0].Id);
    }   

    // Assert actual equals expected
    {
        System.assertEquals(expected, actual);
    }
}   

// Test 2: Query Accounts with Contacts
{ 
    final Integer expected = Limits.getQueryRows() + 1 + contacts.size(); // Current Query Rows + Account + Contacts    

    final Account[] accounts = [
        SELECT 
            Id, 
            (
                SELECT Id
                FROM Contacts
            )
        FROM Account
        WHERE Id IN (
            SELECT AccountId
            FROM Contact
            WHERE Id IN :contacts
        )
    ];  

    final Integer actual = Limits.getQueryRows();  

    // Assert results are valid
    {
        System.assertEquals(1, accounts.size());
        System.assertEquals(account.Id, accounts[0].Id);
        System.assertEquals(contacts.size(), accounts[0].Contacts.size());
    }   

    // Assert actual equals expected
    {
        System.assertEquals(expected, actual);
    }
}

// Clean up data
{
    Database.delete(contacts);
    Database.emptyRecycleBin(contacts);

    Database.delete(account);
    Database.emptyRecycleBin(account);
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.