SOQL vs SOSL – Which to use and when?

SOQL and SOSL both search Salesforce for different types of information. Often times, when first starting with Salesforce, it can be unclear which to use and when to use each one. This begs the question:

  • What arguments can be made to use SOQL or SOSL?
  • When would you use one over the other?
  • What is the main difference?

Let’s make a quick comparison of the two:

Definition

SOQL (Salesforce Object Query Language)

Use the Salesforce Object Query Language (SOQL) to construct simple
but powerful query strings in the queryString parameter in the query()
call, in Apex statements, in Visualforce controllers and getter
methods, or in the Schema Explorer of the Force.com IDE.

Similar to the SELECT command in Structured Query Language (SQL), SOQL allows you
to specify the source object (such as Account), a list of fields to
retrieve, and conditions for selecting rows in the source object. SOQL
uses the SELECT statement combined with filtering statements to return
sets of data, which may optionally be ordered.

SOSL (Salesforce Object Search Language)

Use the Salesforce Object Search Language (SOSL) to construct text
searches in the search() call, in Apex statements, in Visualforce
controllers and getter methods, or the Schema Explorer of the Eclipse
Toolkit.

Unlike SOQL, which can only query one object at a time, SOSL enables
you to search text, email, and phone fields for multiple objects
simultaneously.


Speed

SOQL and SOSL have different indexes. An index makes it much faster to filter queries.

SOQL indexes are:

  • Primary keys (Id, Name and Owner fields)
  • Foreign keys (lookup or master-detail relationship fields)
  • Audit dates (such as LastModifiedDate)
  • Custom fields marked as External ID or Unique.

Fields that can’t be indexed in SOQL are:

  • Multi-select picklists
  • Currency fields in a multicurrency organization
  • Long text fields
  • Some formula fields
  • Binary fields (fields of type blob, file, or encrypted text.)

Note that new data types, typically complex ones, may be added to Salesforce and fields of these types may not allow custom indexing.

SOSL indexes are:

This is the one point where my discussion is weak. I simply can’t seem to find Salesforce documentation on the SOSL indexes. I know there are standard fields like Name that are indexed, but I can’t find the documentation for all of it. If anyone can post a comment on this post, I would really appreciate being able to update this post with that information.


Limits

SOQL and SOSL generally have the same limitations, however according to the Governer Limit documentation:

Description Limit
Total number of SOQL queries issued 100
Total number of SOQL queries issued for Batch Apex and future methods 200
Total number of records retrieved by SOQL queries 50,000
Total number of records retrieved by Database.getQueryLocator 10,000
Total number of SOSL queries issued 20
Total number of records retrieved by a single SOSL query 200

In addition:

  • If a SOQL query runs more than 120 seconds, the request can be canceled by Salesforce.

Syntax

SOQL syntax and SOSL syntax differ greatly. For a truly in depth break, please refer to their respective documentation. However, a simple example of each is:

SOQL

SELECT Id, Name FROM Account WHERE Name = 'Acme'

Return all Accounts where the Name is exactly Acme.

SOSL

FIND {Joe Smith} IN Name Fields RETURNING lead(name, phone)

Look for the name Joe Smith in the name field of a lead and return the name and phone number.


What does Salesforce recommend?

Finally, let’s take a look at what Salesforce says is the best option (page 11):

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.
  • You want to count the number of records that meet specified criteria.
  • You want to sort results as part of the query.
  • You want to 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 may or may not be related to one another.
  • You want to 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.

Some additional considerations when using SOQL or SOSL:

  • Both SOSL search queries and SOQL WHERE filters can specify text to look for. When a given search can use either language, SOSL is
    generally faster than SOQL if the search expression uses leading
    wildcards or a CONTAINS term.
  • In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause may
    be indexed. In this situation, decompose the single query into
    multiple queries each with one WHERE filter and then combine the
    results.
  • Executing a query with a null in a WHERE filter makes it impossible to use indexing. Such queries must scan the entire database to find
    appropriate records. Design the data model not to rely on nulls as
    valid field values.
  • 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 the nulls and avoid the query if necessary.

When designing custom query/search user interfaces, it is important to
follow these guidelines:

  • Keep the numbers of fields/searched to a minimum. In LDV environments, querying a large number of fields in the same query can
    be difficult to performance tune.
  • Determine whether SOQL or SOSL or a combination is appropriate for the search.

Conclusion?

Long story short, both of these languages can be very useful. To quote a discussion from the Force.com Discussion Boards:

SimonF

SOSL can search multiple object types, which requires multiple
separate queries in SOQL, in addition all the relevant fields are
already text indexed for SOSL, but the same fields don’t have DB
indexes, so SOQL queries against them will be slower. If you have a
lot of data, these differences will be much more apparent.

hemm

SOQL should be used when you need precision in what is returned. With
Salesforce functionality being so business process driven, precision
is usually very important and that’s why SOQL is used more often. It
can also be used to craft a search-like query, but it’s probably meant
more for precise queries.

SOSL can be used when precision is not as
important and when you find yourself constructing a crazy WHERE clause
in a SOQL query. It might just be easier to use SOSL. SOSL can give
you a bit more assurance that records you want returned will be even
if you end up with more data to sift through. With SOQL, you are
going field by field to match criteria and you might exclude records
you don’t want excluded. Also, if you add new fields to the system,
SOSL will pick up on those and search them whereas SOQL will not.

Use your judgement, but don’t rule out SOSL or SOQL, use them both!

Note: This is a repost of my original question and answer which can be found on the Salesforce StackExchange.

No comments yet.

Leave a Comment