[SalesForce] Efficiency when querying data with dynamic SOQL vs. formula fields

I am coding a search functionality where I have several parameters to search for (Account Name, Location Name, several kinds of dates, etc.

My question is, when filtering the data in the SOQL query string doing something like the following:

if(accountName != null) queryString += ' AND Location_Account_Name__c LIKE \'%' + accountName+ '%\' ';

Is it better (for robustness and performance) to filter by Location_Account_Name__c being this a formula that points to Site__r.Account__r.Name. or better to actually reference Site__r.Account__r.Name in the query string? no difference?

I would appreciate any insight on this, as the complexity of the query is growing quite fast…

Thanks!

Best Answer

Based on the documentation, the reference to the actual field Site__r.Account__r.Name looks the way to go but it is not clear-cut. (And with small numbers of records you probably won't see any difference.)

This is because (from Working with Very Large SOQL Queries):

primary keys (Id, Name and Owner fields), foreign keys (lookup or master-detail relationship fields), audit dates (such as LastModifiedDate), and custom fields marked as External ID or Unique

and (from Force.com SOQL Best Practices: Nulls and Formula Fields):

Force.com cannot index [formula] fields that: Reference other entities (i.e., fields accessible through lookup fields)

But there is this caveat (from Developing Selective Force.com Queries through the Query Resource Feedback Parameter Pilot):

Leading '%' wildcards are inefficient operators that also make filter conditions unselective. As a best practice, use SOQL or remove the leading '%'; although the Force.com query optimizer does consider using an index even if you do use this leading wildcard, it will choose to use that index based on the number of characters in the search string and the number of matches that result. For these reasons, it usually chooses to do a table scan. When you're querying millions of records, the performance that a selective index gives you can be significantly better.

An index allows a row to be found without the database having to search through every table row (a table scan) and can make a large difference to performance.

So it appears that by not using formula fields you open up the possibility that any pre-existing index is used and you can also request Salesforce support to add custom indexes and they will be used. But the wildcards you are using may cause those indexes to be ignored.

If you have an org with a representative amount of data in it, probably the best thing to do if this optimisation is important to you, is to create some unit tests and compare the results. As well as measuring the execution time you can get some basic information about the resulting query via the Developer Console Query Plan Tool (How To & FAQ).

Related Solutions

[SalesForce] Force Dynamic Soql results to include fields with null value

For me too, this behavior caused a lot of trouble.

I found not much clear documentation, but my guess is that the omission of null values is an optimization strategy of salesforce. In most cases this omission has no impact, but in lots of cases it saves memory as a very limited resource of the platform.

After some research best thing I got are the answers here: Apex JSON.serialize() with null values (RELOADED) - which cleard the backgrounds but actually didn't solve my problems.

Now my workarounds for these issues are

either to use schema methods to generate a list of all fields
or generate a list of fields (to be selected) manually

In both cases I have a List<String> of field-names. Then I create the SOQL string (from the list), execute my query and receive the sparse results.

Now I still have the field-list created before. Depending on the use case I either use this list for iterations in Apex together with SObject.get() or if I need it for JSON I render the fields as a second JSON string and let the JSON-consuming logic (in my case mostly javascript) iterate over the field-list with lookups to the result-list instead of using the result-list directly.

This pattern helped me to solve all the challenges I encountered so far and my first strong displeasure with the sparse results now changed and I actually even like them. For huge results you'll probably get a more efficient solution with sparse results instead of using full populated results.

[SalesForce] Forming a dynamic SOQL with multiple fields in WHERE clause

The simplest and most obvious path is to build the collections as literals, rather than variable bindings. For example, if your email collection contained foo@example.test and bar@example.test, you could filter:

WHERE Email__c IN ('foo@example.test', 'bar@example.test')

This approach to filtering will allow you to include an arbitrary number of collections, and they do not require concrete bindings.

If all of your collections can be cast to strings, this approach will be greatly simplified. Change your structure to Map<String, Set<String>>(), then your literal clause building might look something like:

public String buildWhereClause(Map<String, List<String>> filters)
{
    return String.join(buildSubclauses(filters));
}
public static List<String> buildSubclauses(Map<String, List<String>> filters)
{
    List<String> filters = new List<String>();
    for (String field : filters.keySet())
        filters.add(buildSubclause(field, filters.get(field));
    return filters;
}
public static String buildSubclause(String field, List<String> values)
{
    Set<String> sanitizedValues = new Set<String>();
    for (String value : values) filters.add(sanitize(value));
    return String.escapeSingleQuotes(field) + ' IN (' +
        String.join(new List<String>(sanitizedValues), ',') +
    ')';
}
public static String sanitize(String value)
{
    return String.isBlank(value) ? 'null' :
        '\'' + String.escapeSingleQuotes(value) + '\'';
}

It should be obvious how much more complicated this approach might grow if you need to support other field types. I explored this approach (and the more complicated version which does support all primitive field types) in a FluentQuery library I wrote, and specifically you can look at its ValueFormatter to learn more.

Best Answer

Related Solutions

[SalesForce] Force Dynamic Soql results to include fields with null value

[SalesForce] Forming a dynamic SOQL with multiple fields in WHERE clause

Related Topic