1

In our org, there are 220k existing user record,

Based on this previous code :

public static void assignOwnerId(List<Inventory__c> invList){

    List<User> userList = new List<User>([SELECT SAP_External_Id__c, Id 
                                          FROM User
                                          WHERE SAP_External_Id__c > '0' ]);

    Map<String, String> userMap = new Map<String, String>();

    for (User userLoop:userList) {
        userMap.put(userLoop.SAP_External_Id__c, userLoop.Id);
    }

    for (Inventory__c invLoop : invList) {  
        invLoop.RecordTypeID = ttiProductRecId; 
        if (invLoop.SAP_Account__c  != null && userMap.get(invLoop.SAP_Account__c) != null) {     
            invLoop.OwnerID = userMap.get(invLoop.SAP_Account__c);
        }   
    }
}        

I encountered this error : System.QueryException: Non-selective query against large object type (more than 200,000 rows) upon inserting records through jitterbit.

And revamp this code to :

public static void assignOwnerId(List<Inventory__c> invList){


    Set<String> sapAccountNumbers = new Set<String>();
    for (Inventory__c invLoop : invList) {  
        if (invLoop.SAP_Account__c  != null) {     
            sapAccountNumbers.add(invLoop.SAP_Account__c);
        }
    }

    if (sapAccountNumbers.isEmpty()) {
        return;
    }

    Map<String, Id> userMap = new Map<String, Id>();
    for (User userLoop : [SELECT SAP_External_Id__c, Id 
                          FROM User
                          WHERE SAP_External_Id__c 
                          IN :sapAccountNumbers]) {
        userMap.put(userLoop.TTI_SAP_Cust_No__c, userLoop.Id);
    }

    for (Inventory__c invLoop : invList) {  
        invLoop.RecordTypeID = ttiProductRecId; 
        if (invLoop.SAP_Account__c  != null && userMap.containsKey(invLoop.SAP_Account__c)) {     
            invLoop.OwnerID = userMap.get(invLoop.SAP_Account__c);
        }   
    }
}

And tried to my scratch org to load some records in user object and working as expected. So my question is, if there's any other workaround to test it to assure that the error that I encountered will not appear again (System.QueryException: Non-selective query against large object type (more than 200k)) ? Like for example to create 220k records in user object as test data in the test class. Any help will appreciate!

3

If the SAP_External_Id__c is actually setup as an external id (which means it's indexed), then you should be in good shape with the adjustment you made. When you do a > 0, SFDC has to scan all rows to evaluate that condition. By using the IN selector, you're using the index thus it's selective.

This article, Working with large data volumes, will give you a good idea of what's going on in the background.

Take a look at this, Query optimization cheat sheet. In the "Index Selectivity Exceptions" section, you'll see some items to avoid when you're dealing with a larger scale object.

As far as unit testing goes, the only way you could re-create that kind of data volume is through batch processing during your test setup. SFDC sets limits of 10k DML statements in a single transaction. I think that may be a bit of overkill for this particular issue.

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.