2

Question: Can anyone help me understand which is more expensive in batch apex query locator, given an opportunity record has 100 million records.

I have custom date (custome_date__c) field in campaign object which can have null value. NOTE: Assume campaignId is set which is passed to the batch apex constructor and evaluated in query locator.

Option 1: SELECT campaign.Custome_Date__c, Id, Name from Opportunity where CampaignId = :campaignId

Option 2: SELECT campaign.Custome_Date__c, Id, Name from Opportunity where CampaignId = :campaignId AND campaign.Custom_Date__c = null

What I am not understanding from the documentation is that is the scan for date null limited to CampaignId in the where clause or regardless it will do a full table scan.

https://developer.salesforce.com/blogs/engineering/2013/02/force-com-soql-best-practices-nulls-and-formula-fields.html

Improve this question
2
  • I meant in my question opportunity object with 100 million records.
    – Arup Sarkar
    Feb 22, 2016 at 20:38
  • this problem is readily testable by using DC and QueryEditor to see how long each query takes and the corresponding QueryPlans
    – cropredy
    Feb 23, 2016 at 0:36

1 Answer 1

Reset to default
2

I'm not certain in what context you mean "more expensive". The former would be more expensive in terms of batches processed while the latter would require longer query processing time. That field of course won't be indexed unless you have Salesforce create a "skinny" table for you so the query wouldn't likely be considered any more "selective" than the former because all records in that collection of Ids would need to be run through to determine whether the custom date field value is equal to null. That being said, it's when you're looking for a != that salesforce especially has a great deal of difficulty with queries as I understand the documentation.

From a batch processing point of view, if you only match on the Ids in your query and do the sorting yourself in the batch job, you'll be processing every Id in the query. So, for every null custom date value (records that you're looking to operate on), if you have the query engine locate them, you're going to need to run fewer batches to complete the job and you'll need fewer queries to complete it.

The real question will be whether your query is "selective" enough. That's something you'll need to determine using the query optimization tool or simply by testing the query to see if it returns an error on you.

Improve this answer

You must log in to answer this question.

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