Should I generate external IDs when not needed to avoid potential query issues later?

Question

I have a Currency Rate table with a few fields like so:

Currency_Definition__c : M-D(Currency Definition)
As_of__c  : Date
FX_USD__c : Decimal 
Key__c    : String(11) ExternalId

This table gets updated daily with new rates for a couple dozen currencies. The Key is a combination of the Currency ISO code (from the parent object) and the date, e.g., GBP20170131, and is used as a unique key for upserting rates, and for locating rates quickly from other triggers. The Key__c field is automatically populated by the batch process that creates these rates each day. It will be unique as we upsert all records on that value, although at present the field is not declared unique, only ExternalId.

We are now planning to expand this object by adding a Rate_Type__c picklist, with two values: Daily and Spot. All of the existing records are Daily rates. Spot rates will be special, per-transaction rates, created and managed by the system. By storing both types of rates in one table, existing objects can link to either type of rate, and existing formulas and code will just work.

I don't need a Key value for these Spot rates. They will be created (and occasionally deleted) automatically by a trigger, and there's no need to query as there is for Daily Rates - if you need a spot rate, your object will already link to it, thanks to the trigger. But, this table could potentially grow to contain many records, and I will continue to query the table for Daily Rates via the Key__c external Id field. I'm concerned that many Spot rates could lead to enough nulls in that field to cause "Non-selective Query" or similar issues. I should not ever need to query for Key__c = null.

The question: Is this a valid concern, and should I create Key values for these records to avoid all of the nulls in the Key__c external id field? My simplest solution would be to expand Key__c to 18 characters, and for Spot records assign the ID of the related object to the key. But if it's not needed, I'd rather not add all of those values to the index.

Update with Clarifications, 14 Feb 2017: Upon further reflection, my plan to use the ID of a related object as a throwaway key is flawed if I want it to be unique; there could be cases where a related object has multiple multicurrency relationships each with its own spot rate. I'm sure I can come up with something to generate a unique key value, but not sure I should. To clarify:

• Key__c is currently String(11) External ID but is not Unique or Required.
• Daily Rates will always have a Unique key value derived from the data, needed for upserts and queries. Daily rates are managed by a Batch and won't be edited by users.
• Spot rates don't need a key, they are always linked (lookup) from the related record(s). The values are sourced from related records, but all Spot rates are managed by trigger and will not be edited by users.
• If I add a key value for Spot records, I cannot use the same type of key (ISO+Date); that won't be unique for Spots. Expanding the key to differentiate Daily v. Spot doesn't solve the problem.
• The only reason I'm considering assigning key values to Spot rates is to avoid having many nulls in Key__c, potentially leading to "Non-selective Query" or similar issues, but I'm not sure this is a valid concern or reason.
• I will only query by Key__c = :value if value is non-null (i.e., only for Daily rates).
• Adding unique key values for Spot rates is somewhat non-trivial, but I can do it if there's a risk of query errors should I not do it.
• I could generate non-unique key values for spot rates more easily, and there would not be much overlap; most values would be unique, some values might occur 2 or 3 times.
• I don't know if there is particular value in making Key__c a Unique and/or Required field, specifically in light of the above points.

Answer 1

TL;DR

If you are querying on an indexed custom External Id field, the only way you will give yourself non-selective query errors is by including null in your filter collection. You should avoid that regardless of the percentage of indexed values which are null

Scenario 1 (All External Ids Populated)

I started out with a table containing ~110k records. Every record had the External Id field populated. It's interesting to note that in this scenario, even if you include null in your filter collection, your query may still be selective, though it still causes a TableScan and is notably more expensive.

Query Plan 1

• Query:

  SELECT Id FROM MyObject__c
  WHERE ExternalId__c IN ('1', '2', '3')
  

• Cardinality: 3

• Fields: ExteranlId__c
• Leading Operation Type: Index
• Cost: 0.000272...

Query Plan 2

• Query:

  SELECT Id FROM MyObject__c
  WHERE ExternalId__c IN ('1', '2', '3', '4', '5', '6')
  

• Cardinality: 6

• Fields: ExteranlId__c
• Leading Operation Type: Index
• Cost: 0.000544...

Query Plan 3

• Query:

  SELECT Id FROM MyObject__c
  WHERE ExternalId__c IN ('1', '2', '3', '4', '5', '6', null)
  

• Cardinality: 551

• Fields:
• Leading Operation Type: TableScan
• Cost: 0.677476...

Scenario 2 (Most External Ids Blank)

I then nulled out the External Id field for all but 1000 records and ran the same queries for comparison. Now if you include a null value in your filter collection, it is a long way off from being selective.

Query Plan 1

• Query:

  SELECT Id FROM MyObject__c
  WHERE ExternalId__c IN ('1', '2', '3')
  

• Cardinality: 3

• Fields: ExteranlId__c
• Leading Operation Type: Index
• Cost: 0.000272...

Query Plan 2

• Query:

  SELECT Id FROM MyObject__c
  WHERE ExternalId__c IN ('1', '2', '3', '4', '5', '6')
  

• Cardinality: 6

• Fields: ExteranlId__c
• Leading Operation Type: Index
• Cost: 0.000544...

Query Plan 3

• Query:

  SELECT Id FROM MyObject__c
  WHERE ExternalId__c IN ('1', '2', '3', '4', '5', '6', null)
  

• Cardinality: 109206

• Fields:
• Leading Operation Type: TableScan
• Cost: 2.811583...

Answer 2

You have a few options:

Create a formula field that displays text for nulls, then index that formula field

For example, assume the Status field is indexed and contains nulls.

Issuing a SOQL query similar to the following prevents the index from being used.

SELECT Name
FROM Object
WHERE Status__c = ''

Instead, you can create a formula called Status_Value.

Status_Value__c = IF(ISBLANK(Status__c), "blank", Status__c)

This formula field can be indexed and used when you query for a null value.

SELECT Name
FROM Object
WHERE Status_Value__c = 'blank'

Create custom indexes that include null rows by working with Customer Support

Q: Will an index be used if there are null values in indexed field?

A: If a filter condition specifically searches for nulls and the index supports nulls, then the optimizer will consider the index -- it can use the index if the number of nulls in the index is below the selectivity threshold. Starting with winter '13, you have been able to create custom indexes that include null rows by working with salesforce.com Customer Support. All standard indexes automatically include nulls.

Improving Performance by Not Searching on Null Values

In your SOQL and SOSL queries, avoid searching records that contain null values. Filter out null values first to improve performance. In the following example, any records where the treadID value is null are filtered out of the returned values.

// Note WHERE clause verifies that threadId is not null
SELECT Name FROM CSO_CaseThread_Tag__c
      WHERE Thread__c = :threadId AND
      threadID != null

You can check a Knowledge Article Improve performance with Custom indexes using Selective SOQL Queries for more tips

Answer 3

External Id field is always unique even if we do NOT choose Unique checkbox while defining.

For your usecase, you can take one of two approaches.

Approach 1

Put the spot rates in external Id field and extend the External Id field length to 20 (or whatever that suits), where you can either store the SFDC Id or your own formatted ID like this:

Currency and Datetime upto miliseconds.

Approach 2

Not to store spot rates in External Id and let it be blank.

It will not harm until and unless you are making upsert of any spot rates to your system with the blank external id value.

Your not-null daily rate upsert will not be any problem. Only thing, you need to filter out non-null dataset before upserting.

Few days back, I have given an answer about the uniqueness of external Id, Please refer Upserting Data using Data Loader with External ID