2

I'm seeing some very bizarre SOQL performance when ordering by Id across several Sobjects. In this, they seem to take a very long time to select even just Ids when asked to sort by Id. Remove the ORDER BY, or replace it with something else like LastModifiedDate, and it's perfectly fast.

4.times do
  puts Benchmark.measure {connect.rest_query('SELECT Id FROM EventFeed ORDER BY Id ASC LIMIT 100').first}  
end  
0.150000   0.010000   0.160000 ( 50.074192)
0.020000   0.000000   0.020000 ( 51.917470)
0.170000   0.010000   0.180000 ( 50.206374)
0.010000   0.000000   0.010000 ( 50.750225)

That's 50 seconds for each run. Thinking this table must be huge, I checked.

connect.rest_query('SELECT COUNT(Id) FROM EventFeed').first
=> {"totalSize"=>1, "done"=>true, "records"=>[{"attributes"=>{"type"=>"AggregateResult"}, "expr0"=>0}]}

0 records. It's taking 50 seconds for the Sobject to report back on that query, even though the Sobject is empty. Use a different ORDER BY though, and the results are very different.

6.times do
  puts Benchmark.measure {connect.rest_query('SELECT Id FROM EventFeed ORDER BY LastModifiedDate DESC LIMIT 100').first}
end  
0.020000   0.000000   0.020000 (  0.238133)
0.000000   0.010000   0.010000 (  0.162397)
0.030000   0.000000   0.030000 (  0.177381)
0.020000   0.000000   0.020000 (  0.162638)
0.020000   0.000000   0.020000 (  0.241027)
0.020000   0.000000   0.020000 (  0.168832)

So now it's less than a second. This happens with some other Sobjects too (the Feeds in particular; TaskFeed, ContactFeed, etc.). I get fine performance sorting by literally any other field, or sorting by no field, but sort by Id and suddenly the performance is terrible. Sometimes, it helps a bit to sort DESC instead of ASC with the Ids, but not much.

I'm pretty confused by this. I don't know if this is some bug on Salesforce's side or what, but particularly with my first example with an empty Sobject, it really seems like this should not be happening.

UPDATE: As I continue to poke at this, it really seems like it's an issue with 'Feed' type Sobjects: TaskFeed, LeadFeed, EventFeed, ContractFeed, all seem to exhibit this behavior.

0

I believe the reason you're seeing this behavior is because you've made your query "more selective" by sorting on LastModifiedDate which by default, just like Id, is also an indexed field.

Selective SOQL Query Criteria

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

The selectivity threshold is 10% of the first million records and less than 5% of the records after the first million records, up to a maximum of 333,333 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold can be higher. Also, the selectivity threshold is subject to change.

Also, from the same Apex Documentation:

The following fields are indexed by default.

  • Primary keys (Id, Name, and Owner fields)
  • Foreign keys (lookup or master-detail relationship fields)
  • Audit dates (such as LastModifiedDate)
  • Custom fields that are marked as External ID or Unique

A query on two indexed fields will sort MUCH faster than one a single indexed field or on a field which isn't indexed at all.

The above having been said, the fact you're using the REST API probably complicates things and some of this may not entirely apply as there are other things at play. The above primarily applies to pure APEX. How much the REST API complicates the handling or "post processing" of SOQL queries, I don't know. You might find something in the documentation that would tell you.

  • It doesn't make sense to me that a SELECT on an empty Sobject would ever take 50 seconds, much less a simple one-field SELECT with ordering on an indexed column. Especially since this behavior is not matched on non-Feed Sobjects; if I do the same query on Account, for example, it returns in ~.2 seconds. – kensentor Nov 10 '15 at 17:40
  • I can only comfortably address the part of your question related to LastModifiedDate with any degree of confidence. The fact you were doing an AggregateResult query could be part of your issue on the empty sObject, but that's simply a guess. It could just as easily be REST related. I really don't know. – crmprogdev Nov 10 '15 at 18:19

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.