1

I'm trying to get some records from a CustomObject. This is my object relationships:

Object A [ObjectA__c]

-- Child Object B (master-detail link) [ObjectB__c]

My query is like this:

Set<Id> SetOfObjectA_Ids prepared from this Query first using following query: Select MasterField__c From ObjectB__c Where User__c =: CurrentUserId

SELECT Id, Name, XyzFields__c FROM ObjectA__c 
WHERE (ID IN: SetOfObjectA_Ids) OR (OwnerId =: CurrentUserId)

Now my query above with OR condition in filter is taking almost 41 seconds (via JS Remoting call). Now, if I keep everything above intact and just change the filtering criteria like these:

Query 1:

SELECT Id, Name, XyzFields__c FROM ObjectA__c WHERE (ID IN: SetOfObjectA_Ids)

Query 2:

SELECT Id, Name, XyzFields__c FROM ObjectA__c WHERE (OwnerId =: CurrentUserId)

Both these queries are individually taking almost 2.7 seconds only (via JS Remoting).

Can anyone tell me does including OR condition in SOQL makes the query this much slow?

If anyone is considering how much data exists in these two Objects then here are the stats:

ObjectA__c = 34,433 records.
ObjectB__c = 78,128 records.
Improve this question
8
  • 1
    Have you tested how long your query runs if you simply repeat it? You should make sure that you are not falling into some sort of caching trap. Maybe Salesforce had to build some temporary index when you ran your query for the first time which sped up the subsequent partial queries as well.
    – FrankZ
    Commented Nov 21, 2014 at 13:00
  • I've confirmed that as well. I'm using paging in grid to show records but the same speed is encountered on subsequent pages when fetched. Even exactly same query via refresh button takes that much time. I've gone through with documentation that even OR conditions are optimized by salesforce and in my case my filter criteria is already on Indexed fields., it doesn't seem to optimizing anything here other than it is de-optimizing it :-(
    – VarunC
    Commented Nov 21, 2014 at 17:09
  • Varun - I had a similar issue and you might find this blog post useful: cropredysfdc.com/2014/11/19/soql-query-optimization
    – cropredy
    Commented Nov 21, 2014 at 17:34
  • Is this query SELECT Id, Name, XyzFields__c FROM ObjectA__c WHERE (ID IN: SetOfObjectA_Ids) OR (OwnerId = CurrentUserId) supposed to be SELECT Id, Name, XyzFields__c FROM ObjectA__c WHERE (ID IN: SetOfObjectA_Ids) OR (OwnerId = :CurrentUserId) - that is, you need a bind variable for CurrentUserId
    – cropredy
    Commented Nov 21, 2014 at 23:12
  • @crop1645 - Yes it is a bind variable, I mistyped it in question (corrected now) but it is a bind variable already in my code.
    – VarunC
    Commented Nov 22, 2014 at 4:15

1 Answer 1

Reset to default
4

The general guidance to problems such as these is to use the Query Plan Explain feature, introduced in Winter 14.

Using the Developer Console Query Plan (you have to enable it in DC | Preferences), you can see the relative cost of your query. You can experiment easily with different query where clauses, consider the impact of semi-join queries, and more.

Seeing a query plan that comes back with 'Table Scan' is an eye opener and the tool makes it easy to discover this.

For REST clients, you can replace the q= with explain= on your GET to see the query plan(s).

There are several resources from SFDC that can help which hopefully you have found already:

Cheatsheet

Knowledge article

Engineering blog

Improve this answer
1
  • Thanks been trying these tools all day long :-) .. the trouble is neither Dev Console nor Workbench is able to execute my query. Dev console say "Unknown Error Message: Check for javascript errors." with error console showing: "net::ERR_CONNECTION_RESET" as error. And Workbench shows following error on execution: "SSL read: error:00000000:lib(0):func(0):reason(0), errno 104" ... I believe since not able to make use of "Sub-Joins" on my query is also causing error when I try to use Sub-Join query instead of Set<ID> variable, I get "Semi join sub-selects are not allowed with the 'OR' operator"
    – VarunC
    Commented Nov 21, 2014 at 18:43

You must log in to answer this question.

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