Using SOQL and 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.

	SOQL	SOSL
Executes with	Database	Search indexes
Uses the	`query()` call	`search()` call

Use SOQL when:

You know in which objects or fields the data resides.
You want to:
- Retrieve data from a single object or from multiple objects that are related to one another
- Count the number of records that meet specified criteria
- Sort results as part of the query
- Retrieve data from number, date, or checkbox fields

Use SOSL when:

You don’t know in which object or field the data resides, and you want to find it in the most efficient way possible.
You want to:
- Retrieve multiple objects and fields efficiently, and the objects might or might not be related to one another
- 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

Consider the following when using SOQL or SOSL.

Both SOQL WHERE filters and SOSL search queries can specify text you should look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses a CONTAINS term.
SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces) and builds a search index off this. If you’re searching for a specific distinct term that you know exists within a field, you might find SOSL is faster than SOQL for these searches. For example, you might use SOSL if you were searching for “John” against fields that contained values like “Paul and John Company”.
In some cases, when multiple WHERE filters are being used in SOQL, indexes can’t be used even though the fields in the WHERE clause can be indexed. In this situation, decompose the single query into multiple queries, each of which should have one WHERE filter, and then combine the results.

Executing a query with a WHERE filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

For example, the following customer query performs poorly.

1SELECT Contact__c, Max_Score__c, CategoryName__c, Category__Team_Name__c
2FROM Interest__c
3WHERE Contact__c != null
4    AND Contact__c IN :contacts
5    AND override__c != 0
6    AND (
7            (override__c != null AND override__c > 0)
8            OR
9            (score__c != null AND score__c > 0)
10        )
11    AND Category__c != null
12    AND (
13            (Category_Team_IsActive__c = true OR CategoryName__c IN :selectvalues)
14            AND
15            (
16                Category_Team_Name__c != null
17                AND
18                Category_Team_Name__c IN :selectTeamValues
19            )
20        )

(Items preceded by colons, such as :contacts, are Apex variables. See Using Apex Variables in SOQL and SOSL Queries in the Apex Developer Guide.) Nulls in the criteria prevented the use of indexes, and some of the criteria were redundant causing the execution time to increase. Design the data model so that it doesn’t rely on nulls as valid field values.

The query can be rewritten as:

1SELECT Contact__c, Max_Score__c, CategoryName__c, Category_Team_Name__c
2FROM Interest__c
3WHERE Contact__c IN :contacts
4    AND (override__c > 0 OR score__c > 0)
5    AND Category__c != 'Default'
6    AND (
7            (Category_Team_IsActive__c = true OR CategoryName__c IN :selectvalues)
8            AND
9            Category_Team_Name__c IN :selectTeamValues
10        )

For the field Category__c, the value Default is substituted for NULL, allowing an index to be used for that field.

As another example, if dynamic values are being used for the WHERE field, and null values can be passed in, don’t let the query run to determine there are no records; instead, check for nulls and avoid the query, if possible.

A query to retrieve an account by its foreign key account number can look like this (in pseudo code).

1SELECT Name
2   FROM Account
3   WHERE Account_ID___c = :acctid;
4
5if (rows found == 0) return "Not Found"

If acctid is null, the entire Account table is scanned row by row until all data is examined.

It’s better to rewrite the code as:

1if (acctid != null) {
2   SELECT Name
3      FROM Account
4      WHERE Account_Id___c = :acctid
5}
6else {
7    return "Not Found"
8}

When designing custom query-search user interfaces, it’s important to:
- Keep the number of fields to be searched or queried to a minimum. Using many fields leads to many permutations, which can be difficult to tune.
- Determine whether SOQL, SOSL, or a combination of the two is appropriate for the search.