I'm working on an org that will have around 25m opportunities when data migration is complete. I'm trying to anticipate the issues we will face running batch processes against this data volume. We need to run a batch process that will select around 250k opportunities for processing. The query criteria select on the basis of three fields:

  • an indexed custom text field with a value that will identify around 400k records
  • records where isWon=true
  • a picklist value (payment method) which will have low cardinality

My understanding is that no indexes will be used in this scenario because the number of records returned in each case will breach the maximum number of records for an index to be used.

We have looked at the options for increasing the selectivity of the criteria but can't see an obvious way of doing this and meet the customer requirement.

I have two questions:

  • is this scenario a candidate for a skinny table?
  • will a skinny table use indexing in a way that other salesforce indexes won't (does the skinny table in effect become a multi-field index?) so that we can run queries in which an index will return more than 333k records and the index is still used
  • 2
    Have you used the query plan tool? I believe if you remove the isWon=true and the pick list value your query will become more selective. You can filter the results in the execute method and act on only those which you need to. It will slow down the execute method a bit but allow you to execute the query
    – Eric
    Jul 10, 2017 at 10:56
  • Thanks Eric - removing isWon may help but it looks like the other fields are also non-selective - so would mean no indexes used. I suspect the start query will timeout with a table scan at this volume. Could a skinny table help with this?
    – sefg
    Jul 10, 2017 at 11:06
  • You said the custom text field was indexed so it should be fine..I have never had to use a skinny table so not sure if it would help, be needed, be extra work for nothing, or be the solution.,....
    – Eric
    Jul 10, 2017 at 11:07
  • That's correct - the custom text field is indexed but the number of records returned by the criteria will exceed 333K which is the maximum number of records that can be returned otherwise the index is not used..
    – sefg
    Jul 10, 2017 at 11:54
  • 1
    In SF help for example: help.salesforce.com/…
    – sefg
    Jul 10, 2017 at 12:46


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.