Design practises against 50,000 row limit

Question

please see my scenario. I have 2 custom objects A(child) and B(parent>. Date model is such that Account is the master detail parent of A, while B is the lookup parent of A. The objects Account and B are hence related to each other by this object A which acts as a junction.

The requirement was that no two records of the object A, under the same account and same B should have the same name. I implemented this using a before insert,before update trigger on the object A(child). The logic is as follows:

1. Created a Set that will store a concatenated value of IDs and Name
2. Queried all the records of object A from the database(for update-> didn't query the current records)
3. Iterated through the query result, added entries in the Set of the form => Account ID + B Record ID + A Name
4. Iterated through trigger.new and compared the [[ Account ID + B Record ID + A Name ]] of every record in trigger.new, using .contains to see if it exists in the set.
5. If the value exists => addError

I feel like the algorithm is inefficient, because we query all the records of the object A for just this purpose. We are using from 50,000 max query rows in a transaction gov limit. There are other features that follow this on the same trigger too, so I am worried about hitting the 50,000 row governor limit if the number of records of the object A increase to say 10K.

Is there any way to implement this better, may be using duplication rules?

In general, what are the safeguards or design patterns one can put up against hitting the 50,000 row SOQL limit when the number of records in the database increases. Thank you for your patience.

Answer 1

I'll build off of crop1645's suggestion.

If you have a simple formula field, one with no cross-object references, the formula field will have a value available in both Before Insert and Before Update triggers. (I haven't tested this with cross-object formula fields)

A formula field to generate the synthesized key, A.Account__c + A.B__c + A.Name, would meet this criteria. For sake of example, I'll call this formula field compKey__c.

Provided that the lookups to Account and B, as well as the Name of object A, are populated or updated before inserting/updating, you could modify your logic as follows:

• Iterate over trigger.new, storing compKey__c in a Set<String> keys
• [SELECT compKey__c FROM A WHERE compKey__c IN :keys], iterate over the result of the query, storing the compKey__c in a new set Set<String> conflicts
• Iterate over trigger.new once more, checking if compKey__c exists in the conflicts set. If it does, addError()
  • Might be a good idea to check conflicts.isEmpty() to possibly avoid this second iteration over trigger.new

This should be able to create a single block of code that can handle both Before Insert and Before Update triggers.

Formula fields don't have indexes on them by default, which means my example query would result in a tableScan. It is possible to call in to support and have them put a custom index on the compKey__cformula field so the query would not require a tableScan.

If you don't want to go through that though, then a workflow rule to copy the formula field into a different text field (and using that text field in the query instead of the formula field) would be what you want to do.

It's important to be able to avoid the tableScan. Besides being less performant, you will run into a Non-selective query against large object type queryException using the example query if your A junction object ever grows over 100,000 records.