Improve SOQL Performance

SOQL (Salesforce Object Query Language) used to search Salesforce data for specific information. SOQL allows us to specify source object, a list of fields to retrieve and condition for selecting rows in source object. SOQL returns List<Sobject>

To avoid long execution time, non-selective SOQL queries may be terminated by the system to improve the performance.

A query is selective when one of the query filters is on an indexed field and query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

Following fields are indexed by default and when used in SOQL WHERE clause improved the permormance.

1. Primary Key (Id, Name and Owner fields)
2. Foreign Keys ( lookup or master-detail relationship fields).
3. Audit dates ( systemModStamp).
4. Custom fields marked as External ID or Unique.

NOTE: You can raise an case with Salesforce Support to custom Indexes fields upon request.

But Custom index cannot be created on following types of fields

1. Multi-Select Picklists.
2. Currency fields in a Multicurrency Organization.
3. Long text fields.
4. Binary fields (field of type Blob, file, or encrypted text.).

Selective SOQL Queries example:

SELECT Id FROM Account WHERE Id IN (<list of Account IDs>)

SELECT Id FROM Account WHERE Name != ''

SELECT Id FROM Account WHERE Name != '' AND CustomField__c = 'ValueA'

SELECT Id FROM Account WHERE FormulatField__c = 'ValueA'

following rules have to be true in order to index a Formula Field

• The formula field contains fields from a single object only ( not relationship fields).
• The formula field doesn't reference any non-deterministic functions ( e.g. SYSDATE).
• The formula field contains references to Primary Keys(e.g. Id).
• The formula field doesn't reference any non-supported fields for including in indexes.

Difference between query()/ QueryMore()/ QueryAll()

query():  it executes a query against the specific object and returns data that matches the specific criteria.

Syntax: 

QueryResult = connection.query(string queryString);

used in SOAP API, when a client application invokes the query() call, it passes a query string that specifies the object to query, fields to retrieve, and any condition that records need to satisfied. Client application must be logged in with sufficient access rights to query individual objects and specified fields.

The query result object contains upto 500 rows of data by default. If the query results exceed 500 rows, then client application uses the queryMore() call and a server-side cursor to retrieve additional rows in 500-row chunks.

queryMore(): Use this call to process query() calls that retrieve a large number of records( by default, more than 500) in the result set.The query() call retrieves the first 500 records and creates a server-side cursor that is represented in the queryLocator object. The queryMore() call processes subsequent records in up to 500-record chunks, resets the server-side cursor, and returns a newly generated QueryLocator.To iterate through records in the result set, you generally call queryMore()repeatedly until all records in the result set have been processed (the Done flag is true). You can change the maximum number of records returned to up to 2,000.

Syntax:

QueryResult = connection.queryMore(QueryLocator QueryLocator);

Note: A queryMore() call on a parent object invalidates all child cursors in the previous result set. If you need the results from the child, you must use queryMore() on those results before using queryMore() on the parent results.

queryAll(): Retrieves data from specified objects, whether or not they have been deleted.

Syntax:

QueryResult = connection.queryAll(string queryString);

Use queryAll to identify the records that have been deleted because of a merge or delete. queryAll has read-only access to the field isDeleted; otherwise it is same as query().

To find records that have been deleted (in preparation for undeleting them with the undelete() call), specify isDeleted = true in the query string, and for merged records, request the masterRecord.

For example:

SELECT id, isDeleted, masterRecordId FROM Account WHERE masterRecordId = 'recId'

Let me know if the article help.