SOQL Efficiency: Avoid Limits, Boost Performance

SOQL Efficiency: Avoid Limits, Boost Performance

Efficient queries not only perform better, but they help ensure that you don’t run into problems with governor limits. And it can crash a system faster than anything is a poorly performing query. And that is where governor limits come in. Your SOQL query may return so many objects that the limit on heap size is exceeded & an error occurs if you are dealing with a large database. To resolve, use a SOQL query for loop instead since it can process multiple batches of records through the use of internal calls to query and query more.

Ways For Achieving SOQL Best Practices:

Building Efficient & Selective Queries:

For best practice and best performance, SOQL queries must be selective. Selective query means applying the where clause in the query. You can even combine multiple fields using AND and OR clauses. Having more fields in your WHERE clause is a good thing. The less data that your query returns, the better. But what you might not know is that not all fields are the same. Some fields are what you can think of as “indexed” fields. If you use these indexed fields in the WHERE clause, your queries are super-duper fast. 

For all standard and custom tables, certain fields are automatically flagged to be indexed. These fields include the following:

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

Anytime you use one of these indexed fields in your query’s WHERE clause, you’re increasing the chance that your query is considered selective and an index used as opposed to a full table scan.

Common Causes of Non-Selective SOQL Queries:

Using an indexed field in your query doesn’t always make it golden. You can do things in your queries to make them non-selective and thus prone to the dreaded full table scan. When building your queries, always strive to avoid these things.

Querying for null rows :

 Queries that look for records in which the field is empty or null. For example SELECT Id, Name FROM Account WHERE Custom_Field__c = null

Negative filter operators :

Use operators such as !=, NOT LIKE, or EXCLUDES in your queries. For example: SELECT CaseNumber FROM Case WHERE Status != ‘New’

Leading wildcards :

Queries that use a leading wildcard, such as this: SELECT Id, LastName, FirstName FROM Contact WHERE LastName LIKE ‘%smi%’

Text fields with comparison operators :

Using comparison operators, such as >, <, >=, or <=, with text-based fields. For example SELECT AccountId, Amount FROM Opportunity WHERE Order_Number__c > 10

Query Optimizer:

By using the developer console we can speed up our queries. The developer console gives you a behind-the-scenes peek into how the Query Optimizer works. The Query Plan tool isn’t enabled by default. Enable it by doing the following.

• From Setup, select Your Name > Developer Console to open Developer Console.
• In the developer Console, select Help > Preferences.
• Set ‘Enable Query Plan’ to TRUE.
• Click Save.

Once enabled in the Developer Console, you can access the Query Plan Tool in the ‘Query Editor’ tab of the console.To use the Query Plan Tool, simply enter a query in the Query Editor and press Query Plan to display all query operations and the cost of each for the SOQL provided.

For more information click here.

Avoiding querying on formula fields:

Just because you can filter queries using a formula field doesn’t mean that you should, however. By default, formula fields don’t have underlying indexes, so they require full scans to find target records. They also have to calculate values on the fly because actual values are not stored in the database. So when an object has a lot of records, queries that filter using a formula field can be painfully slow.

To get around the pre-Summer ’12 index limitations involving formula fields, many customers created workarounds. For example, you might have created triggers or workflow rules to store formula field values in a separate custom field that you could index, and then built queries that filtered using the custom field instead of the formula field. That strategy works, but it requires overhead and is not intuitive to developers.

Since the Winter ’13 release, you have been able to contact salesforce.com Customer Support to create a custom index on a formula field, provided that the function that defines the formula field is deterministic.

Here are examples of common non-deterministic formulas. Force.com cannot index fields that:

1. 1. Reference other entities (i.e., fields accessible through lookup fields)
   2. Include other formula fields that span over other entities
   3. Use dynamic date and time functions (e.g., TODAY, NOW)

Custom Indexes Containing Null Rows:

Customers often need to query an object and find only the records in which a certain field is empty (i.e., null). That sounds simple enough, but when your query targets an object that has a lot of records, consider the performance implications. By default, underlying Force.com field indexes don’t include nulls. Therefore, queries that contain custom_field__c =Null need full scans, which again can be painfully slow.

Since the Winter ’13 release, you have been able to work with salesforce.com Customer Support to create custom indexes that include null rows. Even if you already have custom indexes on your custom fields, they need to be explicitly enabled and rebuilt to get the empty-value rows into index tables.

Note that this option does not apply to picklists, external IDs, and foreign key fields. If you need to query on a null external ID field, you can work with salesforce.com Customer Support to create a two-column (compound) index instead.

Delete Record from Recycle Bin:-

Don’t Forget about deleting records in your recycle bin. Deleted records can affect your query performance. There are two ways to resolve this issue.

1. 1. Add IsDeleted = false to your queries.
   2. Empty your recycle Bin.

LastModifiedDate vs SystemModStamp:-

The SystemModStamp is indexed, but the LastModifiedDate is not. The Salesforce query optimizer will intelligently attempt to use the index on SystemModStamp even when the SOQL query filters on LastModifiedDate. However, the query optimizer cannot use the index if the SOQL query filter uses LastModifiedDate to determine the upper boundary of a date range because SystemModStamp can be greater (i.e., a later date) than LastModifiedDate. This is to avoid missing records that fall in between the two timestamps.

For more information click here.

SOQL injection:-

There are a number of techniques you can use to prevent SOQL injection:

1. Avoid using dynamic SOQL where possible, instead use static queries and binding variables
2. Use string.escapeSingleQuotes() for dynamic SOQL
3. Type casting
4. Replacing characters
5. Whitelisting

SOQL Vs SOSL:- 

A SOQL query is the equivalent of a SELECT SQL statement, and a SOSL query is a programmatic way of performing a text-based search.

Use SOQL when:

1. You know in which objects or fields the data resides.
2. You want to:

1. Retrieve data from a single object or from multiple objects that are related to one another
2. Count the number of records that meet the specified criteria
3. Sort results as part of the query
4. Retrieve data from number, date, or checkbox fields

Use SOSL when:

1. 1. You don’t know in which object or field the data resides, and you want to find it in the most efficient way possible.
   2. You want to:
      
      1. Retrieve multiple objects and fields efficiently, and the objects might or might not be related to one another
      2. Retrieve data for a particular division in an organization using the divisions’ feature, and you want to find it in the most efficient way possible

However, for each Apex transaction, the governor limit for SOSL queries is 2,000; for SOQL queries it’s 50,000. So if you need to retrieve more than 2,000 records, SOQL is the better choice.

Avoid SOQL inside FOR Loops:-

Avoid SOQL Queries or DML statements inside FOR Loops to avoid Salesforce governor limits.

In order to protect shared resources, Salesforce enforces a maximum number of DML statements that can be executed inside a single transaction. This is part of the Governor’s limits. If a DML statement is nested inside a loop’s body (For/While/Do-While) it might be executed more times than the Governor limit allows, making the code fail.

Thus it is a best practice to not have DML statements nested in the body of loops and instead perform the DML operation on a list of sObjects.

This rule raises an issue when it detects DML statements inside a loop. In such case, we can use Map of Subjects to get the values and avoid SOQL queries in the for loop.