custom setting getAll() versus SOQL

Question

Does anyone have any background on which and when using the built in getAll() method for custom settings is better than just using traditional soql?

I am aware that getAll retrieves from the cache where-as soql does not, is there some point at which (due to number of rows/size of custom settings) it is quicker to use soql.

Currently I am working with a custom setting that has around 500 rows and these are occasionally refreshed (deleted down and recreated daily). I am having some slow performance, and have noticed that getAll also gets the deleted values, which I then need to filter out.

Answer 1

I'd say it depends on how you are using the Map<String, CustomSetting__c> that getAll()returns and how may list records there are.

If you are looping over the map values() searching for a single record based on a field other than the Name then SOQL would probably serve you better as you could just grab the required records.

One thing to note with the SOQL approach. It will cost you one of your 100 synchronous SOQL queries governor limit. Where as the cached getAll() is free from that but may not have the absolute latest data. I assume that the caching is only for the duration of the transaction (TODO: confirm this is actually the case). See the comment from @ca_peterson that the underlying implementation is based on memcached.

If you can access the Map by the Name key then the Map has some good advantages over SOQL if you need to pick out several values. Even better would be pulling out individual CustomSetting__c records by name using getInstance(dataset_name)

I'm going to go out on a limb here and say that the cache will be much faster than a SOQL query in getting a raw list of all the possible list values. Of course, it's hard to say what other caching Salesforce has got going on internally.

I did a quick test with a list custom setting to get a list of all values:

• SOQL query for all fields: 19:22:33.249 to 19:22:33.251 = ~2 ms
• .getAll().values(): 19:22:33.251 to 19:22:33.252 = ~1 ms

There is a slight advantage to the cache here, but that doesn't take into account how much extra processing you will need to do on the results.

So, in conclusion, it depends on how many of those list custom setting values you want and if you can access them by name. You will need to try both approaches on your data to see which works best while taking into account if you can spare the extra SOQL query.

Answer 2

Summary - getAll() is >10x faster, but SOQL is still fast

Based on my testing scenario, using custom settings instance methods offer ~40x performance improvement, in addition to the advantages of not consuming a query limit. However, both are still pretty fast so this sort of tuning probably isn't the best focus of optimization. This increase is must less pronounced for the first access (assuming the records are only put in memcache as accessed).

Test Scenario

Testing performed with a custom setting with a checkbox, text(2), and text(3) field with 250 records in a dev sandbox with debugs logs enabled.

SOQL Test

List<CustomSetting__c> settings;
Long startTime = DateTime.now().getTime();
for(Integer i = 0; i < 100; i++) {
    settings = [
        SELECT Id, Checkbox__c, Text2__c, Text3__c, Name, CreatedDate,
            CreatedById, SystemModStamp, LastModifiedById, LastModifiedDate 
        FROM CustomSetting__c];
}
Long stopTime = DateTime.now().getTime();
Long totalMs = stopTime - startTime;
system.debug('==>totalMS: ' + totalMs);

Custom Settings getAll() Method

Long startTime = DateTime.now().getTime();
for(Integer i = 0; i < 100; i++) {
    CustomSetting__c.getAll();
}
Long stopTime = DateTime.now().getTime();
Long totalMs = stopTime - startTime;
system.debug('==>totalMS: ' + totalMs);

Results

SOQL

Completion times were 887ms, 1055ms, 1014ms, 965ms, and 925ms for an average of 9.692ms/iteration.

Custom Settings getAll() Method

Completion times were 218ms, 17ms, 18ms, 45ms, and 18ms for an average of 0.632/iteration including all results. Excluding the initial result outlier an average of 0.245ms/iteration.