+ Start a Discussion
SteveEthosSteveEthos 

SOQL Slow with an OR condition

I have found a SOQL Statement that is slow with an OR condition, but fast if I take it out.

 

This always takes 3-4 seconds (returning 0 rows):

 

select count() from pat_Authorization__c where (r_episode_payer__r.r_subscriber__r.r_patient__c = 'a0999999999999' OR r_payer__r.r_patient__c = 'a0999999999999') AND  is_deleted__c = false 

 

(the a0999999999999 is a Salesforce ID)

 

 

This is fast: 

 

select count() from pat_Authorization__c where (r_episode_payer__r.r_subscriber__r.r_patient__c = 'a0999999999999') AND  is_deleted__c = false 

 

 

This is fast: 

 

select count() from pat_Authorization__c where (r_payer__r.r_patient__c = 'a0999999999999') AND  is_deleted__c = false 

 

 

Each of the "r_" fields are of type Lookup.

 

 

So, by testing each of the logical conditions separately, I have determined that one specifically is not the culprit.  So, why does adding the OR condition suddenly make it go slow?

 

 

I am also using the same WHERE clause on a SELECT id, name, .... FROM pat_Authorization__c where (r_episode_payer__r.r_subscriber__r.r_patient__c = 'a0999999999999' OR r_payer__r.r_patient__c = 'a0999999999999') AND  is_deleted__c = false 

 

 

So, I need to find a way to get the SOQL fast and to check both conditions and return the set.  If there was such a thing as UNION, then I could use that.

 

Thoughts?

Steve