0

I have a soql query on the asset object. There are a few million records. I have a limit clause at 100 and some fields in query but it still takes a while to get query results.

Are there any tricks to getting faster results. For example, I am looking mostly at sale dates in last 120 days, would more filters make it faster?

Edit: My Query

Select fields from Asset where product__c like '"+prod+"%' and Reseller__c like '"+res+"%'and SerialNumber like '"+serial+"%'"+ "and Distributor__c like '"+disti+"%'and End_User__c like '"+enduser+"%' and SpaId__c like '"+spanum+"%'

Update: I did index a couple fields and changed from contains to starts with as seen above. This did help a lot. Significantly faster when searching the indexed fields which should be the majority of the time.

I did not request index for purchase date because in the documentation, indexing will not work for my use case because Purchasedate has around 800k results.

Thanks for the help.

5
  • 1
    Maybe this can help you help.salesforce.com/… Apr 23, 2021 at 16:07
  • @OlehBerehovskyi If I am reading this right, if using the like operator, it will slow down the query result. Am I understanding this well? Apr 23, 2021 at 16:12
  • 2
    If you look at the query plan (through the developer console or through the REST API), what is the "cost" and "leading operation type" rows for your query? Lower costs are ostensibly faster.
    – Derek F
    Apr 23, 2021 at 16:52
  • A few things can help: indexes, skinny tables, making the query more selective refer to developer.salesforce.com/docs/…, concerning your question about "like operator", indeed, like is quite expensive in terms of records selection.
    – Marcin Sz.
    Apr 25, 2021 at 18:25
  • I could not truly follow the query due to formatting issues, but it seems that you are using like for foreign keys such as product__c, is there a reason to not use equals for those? Apr 26, 2021 at 19:07

1 Answer 1

1

Specific advice would really require you to post the query, but I can give some general tips on how to optimize queries in SOQL.

So the problem with a LIMIT clause is that (as far as I've studied) it doesn't usually improve query performance, because often the database still has to do a full collection scan (look at every record in the table) before it can return the results in a particular sorted order. For example, if you want the first 100 records, sorted by a normal text field in alphabetical order, it still has to look at all the records in the table to put them all in order before it can return the first 100.

I'm unclear on whether or not the database will perform better when there is no defined order. This post seems to indicate that there is no default order, so theoretically the system could grab the first 100 records it finds which match the WHERE clause, and stop the full collection scan. The downside of this is that it may return different records on the run of the same query, if they are truly unordered, so the SOQL engine may still do a full collection scan for this reason, or any other reason. I'm not sure we have the implementation details to know.

The supported way for making sure that your queries are optimized is to filter based on an indexed field with a high cardinality (a large number of distinct values, like a date field, or an id field). Indexes in SOQL use special data storage techniques to provide quick access to a given value; the important thing is that an index will be faster the more unique values there are in the database. So id fields are extremely fast, because there's only one of each in a given table. Date fields, somewhat less so. Picklist fields don't typically get indexed, because there are only a few total values in the database.

Unfortunately, Salesforce gives you very little flexibility in indexing your fields. You have a few options:

  1. Id fields are automatically indexed
  2. Text fields can be indexed by checking the "External Id" column
  3. You can ask Salesforce support to add an index to a specific field. They may or may not grant this request, and in my experience it takes a lot of back and forth.

Various standard fields are also indexed. CloseDate on the opportunity, the CreatedDate and LastModifiedDate fields on standard and custom objects, and various other fields. I usually try to structure my solution design around the ability to sort and filter based on one of these standard date fields, since (sadly) we cannot index date fields without the help of support.

In your case, it sounds like there is a "sales date" field of some sort you're looking at. If this is a standards Salesforce field, there's a decent chance that it's indexed; you can google around to see. If it is, then yes -- filtering on only the last 120 days would probably improve the query dramatically, if most of the records in the database are older than that.

There's another technique I'm trying out on a recently created object. We haven't gotten enough records yet for definitive results, but I'll post it here as food for thought. Most of our large tables include transactional records, which are relevant for a period of time, but eventually move to some sort of a closed status, where they're relevant only for historical reporting etc. So I'm trying out a technique for viewing only the records that are not yet closed, by marking the closed ones in an indexable fashion.

Basically, I created a text field as an external id (making it indexable). Then I created another auto-number field (not indexable). When the record is closed, I copy the auto-number value to the external id field. SOQL queries then are theoretically able to reduce the result set by searching for NULL in the indexed field. That may or may not be helpful; it really depends on how Salesforce has implemented their query plan for this kind of a scenario; time will tell. But it was either that, or ask Salesforce to index a close date field, so I figured I'd give this a try.

Hope that helps. Your basic instinct of limiting by date is typically the way to go, if you have a date field that is indexed.

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.