SOQL, Salesforce Object Query Language, is a proprietary language which aims to query Salesforce SObjects. There are many ways to write and test those queries, we will explore some with some use cases.

Query Editor

Query Editor is available from the developer console and can be used to:

• Write SOQL queries
• Test query efficiency
• Seacrh record in database

Examples of query:

select Id from User

select Id, Name from User

Debug mode

Debug mode is available from developer console in Debug > Open Execute Anonymous Window and can be used to:

• Write SOQL queries
• Write APEX code
• Follow the execution log

Examples of code:

List<User> usersList = [Select Id from User];
List<User> usersList = [Select Id, Name from User];

Those 2 queries are the same and bring the same amount of records, but there are some subtleties than we are going to see afterwards.

Use Case 01 : Query an SObject where there is no record or the filter used does not return any results

User user = [Select Id from User where Name = 'DO NOT EXIST'];
Id userId = [Select Id from User where Name = 'DO NOT EXIST'].Id;

What’s happening here?

• The query is well written
• The SObject exists and the mapping is correct
• The query filter is correct

But:

• The query will bring no result because there is no records corresponding to the applied criteria
• Salesforce engine will try to assign the result of the query into an instance of the User SObject
• An exception will be fired
  • “System.QueryException : List has no row for assignment to SObject”
• However, the query is valid and accepted at compilation.

Here’s a situation where the query could be executed without throwing exceptions:

User user = [Select Id from User Limit 1];
Id userId = [Select Id from User Limit 1].Id;

How to avoid this situation?

Query an SObject where there is no record or the filter used does not return any results.

List<User> userList = [Select Id from User where Name = 'DO NOT EXIST'];
List<User> userList = [Select Id from User Limit 1];
List<User> userList = [Select Id from User];

What’s happening here?

• The first query will bring no result because there is no records corresponding to the applied criteria
• The second query will bring [0..1] record
• The third query will bring [0..N] records (there is always 1 user in DB)
• Salesforce engine will try to assign the result of the query into an instance of List of User
• For all use cases, we will write queries this way because the list will be always instantiate
• From here, all you have to do is to test the size of the collection to see if there is any record inside

Use Case 02 : Query an SObject where there are many records

User user = [Select Id from User];
Id userId = [Select Id from User].Id;

What’s happening here?

• The query is well written
• The SObject exists and the mapping is correct

But:

• The query will bring many results
• Salesforce engine will try to assign the result of the query into one instance of the User SObject
• An exception will be fired
  • “System.QueryException : List has more than one row for assignment to SObject”
• However, the query is valid and accepted at compilation.

Here’s a situation where the query could be executed without throwing exceptions:

User user = [Select Id from User Limit 1];
Id userId = [Select Id from User Limit 1].Id;

How to avoid this situation?

Query an SObject where there are many records.

List<User> userList = [Select Id from User Limit 1];
List<User> userList = [Select Id from User];

What’s happening here?

• The first query will bring [0..1] record
• The second query will bring [0..N] records
• Salesforce engine will try to assign the result of the query into an instance of List of User
• For all use cases, we will write queries this way because the list will be always instantiate whether or not record exists or there are existing records not corresponding to the applied criterias
• From here, all you have to do is to test the size of the collection to see if there is any record inside

Use Case 03 : Query an SObject where there are many records and play with some Governor Limits.

• Start by creating 60000 records in Account
• Play the following script 6 times by adjusting the “bundle” variable from 1 to 6

Integer bundle = 1;
List<Account> accountList = new List<Account>();

for (Integer i=1;i<=10000;i++){    
    Account acc = new Account();    
    acc.Name = 'TEST_'+bundle+'_'+i;    
    accountList.add(acc);
}

if (accountList.size() > 0){    
    insert accountList;
}

What’s happening here?

• We have created a high volume test sample
• The scripts has been played 6 times to avoid Salesforce Governor Limits
• If you do not know what is Governor Limits, please refer to the documentation

Check the number of records in Account SObject

• It’s not possible to make a count with SOQL where there are a huge number of records, the following script will throw an exception is some cases:

Integer count= database.countQuery('select count() from account');
system.debug('the count is '+ count);

• Alternatively, you can check storage in System Overview menu or use tools like workbench

01 – Execute the following queries and analyse their behaviors:

for (Account acc : [select Id from Account limit 201]){}
for (Account acc : [select Id from Account limit 10001]){}; 
for (Account acc : [select Id from Account limit 50001]){};

What’s happening here?

• Salesforce engine will open one querylocator per record
• Over 50000 records, an exception will be thrown
  • System.LimitException: Too many query rows: 50001
• This approach is recommended for small amount of records
• DML operations inside those loops are strictly prohibited

02 – Execute the following queries and analyse their behaviors:

for (Account acc : [select Id from Account limit 150]){update acc;}
for (Account acc : [select Id from Account limit 151]){update acc;}

What’s happening here?

• Salesforce engine will open one querylocator per record
• Over 150 updates, an exception will be thrown
  • System.LimitException: Too many DML statements : 151
• DML operations inside those loops are strictly prohibited

03 – Execute the following queries and analyse their behaviors:

for (List<Account> accList : [select Id from Account limit 150]){update accList ;}
for (List<Account> accList : [select Id from Account limit 151]){update accList ;} 
for (List<Account> accList : [select Id from Account limit 10000]){update accList ;}
for (List<Account> accList : [select Id from Account limit 10001]){update accList ;}
for (List<Account> accList : [select Id from Account limit 20000]){update accList ;}

What’s happening here?

• Salesforce engine will group records by collection of 200 records max so that records can be proceeded by chunk and high/over usage of resources can be avoided
• Over 10000 updates made over 1 or many DML statements, an exception will be thrown
  • System.LimitException: Too many DML rows : 10001
• DML operations should be done carefully in this case

How to avoid this situation?

Follow these guidelines:

• Although it’s a good practice to use SQL FOR LOOP to handle high volume of records and hence reduce the number of operations by bundling records instead of processing one by one, it has to be used with extreme care and high data volume has to be mastered.
• It’s so easy for code to be modified and be exposed to regression if there is no absolute control over the volumes.
• Data volume can grow overtime, it’s not a truthful information that we can rely on when developing
• General rule is always to strictly avoid SOQL or DML inside LOOP.

Conclusion

Data is the most important thing in every CRM application, we need it in absolutely every process we put in place. Accessing data can seem to be easy but it’s often the most painful part that we are dealing in any implementations. So starting to build your queries with knowledge on the limits and the way to prepare for high volume is a key to success.

Hope you enjoy reading this article, see you soon for the next one ...