4

Will there be any performance impact when using SOSL with where clause?

There are millions of records in one of the custom object. We need to develop custom search functionality where user can select multiple fields and enter search txt against each field. We are having performance issue while using SOQL therefore wanted to switch to SOSL.

Basically I would like to know how where clause works in SOSL, whether it performs the full text search first then apply where clause on the result set?

  • 2
    Also worth reviewing how/when SOSL applies the where clauses. Your results will already have been "shrunk" to a max of 2000 records BEFORE your filters are applied: developer.salesforce.com/docs/atlas.en-us.192.0.soql_sosl.meta/… – Girbot Jan 25 '17 at 18:36
  • Thank you @Girbot, it helped me to come to conclusion. Does this mean even though my search result may not return 2000 records even though there are records matching all the criteria including where clause, because where clause will be applied on top of first 2000 records returned by the full text search. – LakhanP Jan 26 '17 at 2:59
7

As per the Developer Guide on SOSL WHERE clause, it is used to filter the search results:

By default, a SOSL query on an object retrieves all rows that are visible to the user. To limit the search, you can filter the search result by specific field values.

Furthermore, the syntax of the SOSL also suggests that the WHERE clause is applied to search results, yielded by the text search:

FIND {search_query} RETURNING sObject(fieldName WHERE fieldExpression)

Consider the following example:

FIND {John} IN NAME FIELDS RETURNING Account(Id, Name WHERE IsImportant__c=true), Contact(Id, FirstName, LastName WHERE IsPrimary__c=true)

In the example above, one would expect the search to be performed on all Accounts and Contacts, and the filter subsequently applied to the resulting record set (all Accounts and Contacts where term 'John' is found in Name fields), thus eventually returning only the records meeting the expression criteria (all Important Accounts and Primary Contacts where term 'John' is found), rather than initially filtering all accounts and contacts to important/primary ones and then doing a text search on those.



I think in your case using SOSL rather than SOQL might indeed yield performance gains (digression: are all of the searchable fields textual and are they indexed?). If I understood you correctly, your users will be able to enter search terms freely, meaning your search will check against fuzzy matches, meaning you would have to be using wildcards in SOQL conditions. Furthermore, as you mentioned that users will be able to select fields on which to search against, it means you would end up with multiple conditions in WHERE clause. Consider following remarks from the Best Practices for LDV handbook:

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.


Given the problem presented (users being able to select multiple fields and enter search text against each field), I assume you considered translating the search conditions into the WHERE condition in SOSL. Does not sound like a good idea, as you might end up with the following:

...RETURNING YourTargetObject(Id WHERE selectedField1 LIKE %textInput1% OR selectedField2 LIKE %textInput2%)

Apart from the above, the question arises what would you use as the primary search query in such case. Instead of such WHERE filtering, it would seem wiser to have all the user's textual inputs bundled in the SOSL's FIND search term, especially having the following performance consideration from the Force.com SOQL and SOSL Reference in mind:

  • 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”.

So you might find it that the latter could be more efficient than the former:

FIND {textInput1* OR textInput2*} RETURNING YourTargetObject

But to give you a definite answer whether SOQL or SOSL would be more efficient is kind of an ungrateful task, as important details are missing (are the searchable fields only searchable by text, how many fields can the search check against, how complex can the custom search logic become etc). Your best bet would be to pull in some typicall searches users might make and compare both SOQL and SOSL performance.

  • Hi @dino ursic, thank you for the detailed information. – LakhanP Jan 26 '17 at 2:57
  • Hi @dino ursic, there are some fields which are not supported by SOSL e.g. lookup, picklist etc. and some of the fields are not indexed. We know that these things do impact our implementation but just wanted to check whether using where clause in SOSL will be considered for selectivity threshold (efficient use of index for performance). I think it will not consider threshold as where clause is applied on the search result only. developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… – LakhanP Jan 26 '17 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.