What is SOQL?

Salesforce Object Query Language (SOQL) is used to build queries in the Force.com platform.

Salesforce Object Query Language (SOQL), are queries that are used to extract information from a database. They allow us to filter and combine information from a specific object, in a more advanced and specific way. In Salesforce, these queries are a very useful tool to obtain records that will later be processed.

What are SOQL best practices?

As we know, it is possible to manage large amounts of data through SOQL, however, there are some limitations in those processes. To begin, Salesforce only allows 100 queries per transaction. Plus, in each of those queries, only up to 50.000 records can be retrieved. In order to avoid exceeding these limits, it is important to take into consideration SOQL best practices.

SOQL Queries Optimization for Selectivity

The first step to consider is to make our query as selective as possible. A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows.

Example of indexed fields:

  • ID
  • Name
  • OwnerId
  • CreatedDate
  • SystemModStamp
  • RecordType
  • Master-Detail Fields
  • Loopup Field
  • Unique Fields
  • External ID Field  

Salesforce Query Optimizer Utilization

One of the most powerful tools Salesforce offers is Query Plan. If a query is running under this functionality, we can see what happens behind the front end. Like a behind-the-scenes that allows us to visualize the calculations that are made and thus have an idea of how optimized a query is.

‍How to Enable Query Plan

  • From Setup, select Your Name > Developer Console to open Developer Console.
  • In the Developer Console, select Help > Preferences.
  • Select Enable Query Plan and make sure that it’s set to true.
  • Click Save. And in the Query Editor tab, confirm that the Query Plan button is now next to the Execute button.

The most important thing to know is that cost values greater than 1, do not produce selective queries.

SOQL Injection

SOQL injection is a method that allows you to infiltrate code that you do not intend to pass when executing SOQL statements. This occurs when our application relies on user input to construct a final SOQL statement. To avoid this, it is important to:

  • Avoid using dynamic SOQL where possible, instead use static queries and binding variables.

Instead of using this:

String queryOpportunity = 'SELECT Id from Opportunity WHERE StageName=\''+var+'\''; queryResult = Database.execute(queryOpportunity );
Apex

Use this:

queryResult = [select id from contact where firstname =:var]
Apex

Instead of using this:  

String queryCase = 'SELECT Id, SuppliedName from Case'; String whereClause = 'SuppliedName like \'%'+textualSuppliedName +'%\' '; List<Case> whereclause_Cases = database.query(queryCase +' where '+whereClause);
Apex

Use this:

String whereClause = 'SuppliedName like \'%'+String.escapeSingleQuotes(textualSuppliedName)+'%\' ';
Apex

Avoidance of SOQL Within for Loops

This is the wrong way to use SOQL:

List<Account> accountList = [SELECT ID FROM Account LIMIT 5]; for (Account a : accountList ){ List<Contact> cList = [SELECT ID FROM Contact WHERE AccountID = :a.Id]; } System.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries()); System.debug('Total Number of SOQL Queries allowed in this apex code context: ' + Limits.getLimitQueries());
Apex

What if the limit were 500 instead of 5? It could easily reach the SOQL limit per transaction considering that the total number of SOQL queries issued by the salesforce is 100 Synchronous.

Query for Loops Implementation

To avoid reaching record limits per SOQL query, we must use the SOQL for loop:

for (varList : [soql_query]) { //Enter your code here } for (var : [soql_query]) { //Enter your code here }
Apex

Both var List and var must be of the same type as the Objects that are returned by the soql_query.

Difference between list for loop and SOQL for loop in Apex

Using SOQL List for loop:

Create a list of sObject result list first and then loop through the list.

List<Opportunity> oppUpdateList = new List<Opportunity>(); for(Opportunity opp : [Select Id, Name, StageName From Opportunity]){ if(opp.StageName =='Calificacion') { opp.StageName = 'Closed Won'; oppUpdateList.add(opp); } } if(!oppUpdateList.isEmpty()) { update oppUpdateList ; } return oppUpdateList; }
Apex

In this case all records matching a StageName equal to Clasification will be assigned a "Close Won" stage and then added to a previously created list of opportunities.

Using SOQL for loop

Use the SOQL query in the for loop.

List<Opportunity> oppUpdateList = new List<Opportunity>(); for(Opportunity opp : [Select Id, Name, StageName From Opportunity]){ if(opp.StageName =='Calificacion') { opp.StageName = 'Closed Won'; oppUpdateList.add(opp); } } if(!oppUpdateList.isEmpty()) { update oppUpdateList ; } return oppUpdateList; }
Apex

In this case, SOQL for loops can process records one at a time through a single sObject variable, or it can process records in batches of 200 sObjects at a time through an sObject list.

Valuable Examples of SOQL Queries

  • Delete system logs

When your org accumulates too many debug logs, delete some or all of your system logs and monitoring logs. Use the Developer Console’s Query Editor to find and delete the logs using Tooling API.

SELECT Id, Status, StartTime, LogUser.Name, Operation, DurationMilliseconds,Location, LogLength, Application FROM ApexLog where LogUser.Name ='John Doe'
Apex
  • Monitoring Queued Jobs
AsyncApexJob jobInfo = [SELECT Status, NumberOfErrors FROM AsyncApexJob WHERE Id = :jobID];
Apex
  • Monitoring Scheduled Jobs
CronTrigger ct = [SELECT TimesTriggered, NextFireTime FROM CronTrigger WHERE Id = :jobID];
Apex
  • Find the number of users per profile
SELECT count(Id), Profile.name FROM User WHERE User.IsActive = true GROUP BY Profile.name
Apex
  • Find Record Types on an object
SELECT Name, Description FROM RecordType WHERE IsActive = true and sObjectType = 'Account'
Apex
  • Working with Attachments
SELECT ContentDocumentId, LinkedEntityId FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM Account) and LinkedEntity.Type='AccountT'
Apex
SELECT ContentDocumentId, LinkedEntityId FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM Opportunity where StageName = 'Calificacion') and LinkedEntity.Type='Opportunity'
Apex

References