Optimizing Soql on Asset Object

Question

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.

Answer 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.