Apex Code Development You need to sign in to do that
Don't have an account?
SOQL vs SOSL
For some time I've wondered what the argument might be to use SOSL instead of SOQL. I can't find anything that says it's faster, and it's limited in the field types it can search, and the flexibility of the search results. As far as I've seen every situation that could be contrived as a use-case for SOSL can be done very neatly with SOQL.
I suppose what I'm trying to say is, can you convince me that sometimes I should be using SOSL?
Wes
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.
All Answers
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.
Excellent, thanks.
In my development, it's 99% SOQL. Where SOSL has come up is when there is a need to allow an end-user to take a "broad brush stroke" across the data to find a record to work further with. Once found, SOQL queries take over.
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.
SOSL comes up in specific use cases for me, but most of the time SOQL works just fine too.
Thanks, do you mind me quoting you in my book?
Wes
Not at all. Go for it. Although, I might like the Opportunity to "re-quote" if I sound bad. :)
Sure, I'll bookmark this thread in the hope that I'll remember :)
It's important to remember that although you can receive faster results from SOSL, it can just as easily be faster incomplete results. SOSL is limited to 200 records returned. If you are doing a name search against a large database, this won't give the desired results on a common name such as Smith. In addition, due to how Salesforce implemented their searches, it's possible to not find a record at all even though it exists in the database. Salesforce hasn't been able to explain it in a way I can translate but it has something to do with how they read all records and then pair it down to what you have access too. Somehow records get dropped off the result set even if 200 aren't returned.
We are hoping Salesforce fixes this. If they don't, SOSL is rendered useless in a lot of scenarios when you have large volumes of data.
This should help explain how Salesforce retrieves records from the search index to display to different users.
Also, Salesforce has significantly increased the number of results returned per object.
If you still aren't able to retrieve the data you're looking for, contact Salesforce support about optimizing your search index.