6

I have a doubt. Sometimes the developer repeats many queries in different places, but what changes is the number of fields. I sometimes think about centralizing queries, but queries can have fields that you won't use in one place, but can use elsewhere. If I do a query with 5 fields, using "LIMIT 1" and another query on the same object using 15 fields, and also using "LIMIT 1". Is there a performance loss due to the number of fields? And if there is a loss of performance, this loss is insignificant?

For example:

SELECT Id, Name, Type, SLA__c, Active__c, CustomerPriority__c FROM Account WHERE Id =: numId AND LIMIT 1

SELECT Id, Name, Type, SLA__c, Active__c, CustomerPriority__c, NumberofLocations__c, UpsellOpportunity__c, SLASerialNumber__c, SLAExpirationDate__c, First_Name__c, Founding_Date__c FROM Account WHERE Id =: numId AND LIMIT 1
2
  • 3
    If you're matching on ID, there's no need to specify 'LIMIT 1' - there will only ever be one record that matches (if the ID is a valid, current ID for that object - otherwise no records will match) Jan 3, 2022 at 15:22
  • 4
    Commenting instead of answering because I don't have any data/documentaiton to back this up, but... It might take a bit more "clock time" (but not your CPU limit) due to returning more data to the application server. Between that and the 100/200 query limit per transaction, I'd expect the difference to be unnoticable except possibly for extreme cases (you're querying 500 fields and/or 10k+ records). The biggest difference is probably in heap space consumed.
    – Derek F
    Jan 3, 2022 at 15:26

2 Answers 2

7

In my experience, there is effectively no difference whatsoever. Query selectivity is unaffected, so query run time should be as well. If you look at any two queries in the Query Plan where the only divergence is in the SELECT clause, you can prove as much.

The only non-trivial difference will be in heap consumption, but in most contexts, that will be minor. In a typical domain, it might be the difference between topping out at 10% vs 8% of the governor limit. Unless you are querying max length long text areas and/or tens of thousands of records, there just isn't going to be that much data. Consolidating query count is far more important.

4

I can't tell you how much my developer productivity has improved since I consolidated queries into selector class methods and used query composition patterns to ensure related objects' fields are included. See Enterprise Architecture Selector Patterns

Sure, extra fields come back for some/most use cases (that is, the caller doesn't need the fields) but who cares if the query is selective and you don't have data skew or enormous child related lists). After all, the computer should be our servant.

What is useful is to have reusable queries - for example, my ContactsSelector has methods:

  • selectById(Set<Id> ids)
  • selectByEmail(Set<String> emails)
  • selectByCreatedDate(Datetime fromDate, Datetime toDate)
  • ...

The callers reference the methods and assume the query brings back the relevant fields; if not, then add the field to the selector class's query composition field list (or fieldset)

Putting your queries into selector classes allows the query results to be mocked for unit tests and thereby avoid constructing DML records in testmethods (which make your tests run longer)

For the exception situations where the number of records returned could blow up heap, I construct specialized selector methods tailored to minimize fields (often by exploiting fieldsets)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .