8

Existing Logic:

We have a trigger that gets the highest value of a custom Field "UIN__c" on various Objects and increments the value by 1 on each new Record. This Field functions as an ID and is unique.

We use following Query to get the highest value:

Integer.valueOf( database.query('select UIN__c from '+ this.name + ' order by UIN__c DESC NULLS LAST LIMIT 1 ALL ROWS ' )[0].get('UIN__c') );

Problem

At some point, the number of Records of some Objects reaches 200 000. As soon as this happens the logic breaks, as the query being used has to be "selective" to query a large Object. "Selective" in the sense of Salesforce. If we query without being "selective" then we get following error:

Non-selective query against large object type (more than 200000 rows)

To make the Query selective, we must add a WHERE clause and filter by an indexed Field and query less than 30% of total records. But this reduction is conflicting with our goal to get the highest UIN__c of ALL records. Any thinkable filter could exclude the highest UIN__c. We thought querying only the newest records created in the last 365 days, but that would not be bullet proof:

  • a) if NO record is created in the last 365 days, we would not have any result at all.

  • b) if more than 30% new records have been created within the last 365 days, we would get the very same error again.

Our conclusion is that ANY possible where clause would not be bullet proof. Hence the issue would not be avoidable.

Question

Now we are looking for alternatives instead of adding a WHERE to the query. Is there another query we can use to get the highest value of a custom field on a large object? In the database could be any number of records (even millions of records are possible).

Is it worth to try aggregate queries using MAX(UID__c)?

Any other approaches?

Even ideas or conceptual fragments are welcome! We do not expect a complete solution. Also feedbacks of approaches which are obviously NOT feasible will help to rule stuff out.

1
  • Why don't you use a custom autonumber field on each of the object ?
    – Shamina
    Oct 1, 2021 at 19:44

1 Answer 1

0

This design suffers from possible flaws anyways, depending on what you're using UID for. Consider a situation where a MAX(UID__c) record is created, then deleted, and another one is created, you'll end up reusing your UID. This may or may not be an acceptable situation.

You might consider using a Custom Setting to track the value. Use FOR UPDATE to lock the Custom Setting record, set the values in the trigger, and increment as appropriate. I'd recommend using one Custom Setting per object to avoid excessive database contention. This design ensures that a UID would only ever be used once per record, even in the face of deletions.

You can use a spin lock to wait almost indefinitely for the lock, even in a trigger context. Just be aware that this technique may cause issues with the "long concurrent" in high traffic objects with heavy processing (see Execution Governors and Limits).

As an alternative to the above design, you might also choose to generate a "UID" from Crypto.getRandomLong(), which provides a random 64-bit number from a cryptographically secure generator, meaning that collisions have a very, very low chance of occurring until you have hundreds of millions of records (500M is listed at a 0.7% chance of probability at this scale). You can use a simple query to verify if you would collide with an existing record, and it should be considered selective.

The only downside is that you'd have to trim off a few bits, because a 64-bit number can be 20 digits long, and numeric fields are only allowed 18-digits of accuracy; you have to trim off the last 5 bits to fit in that space. This will increase collision rates faster, but it should still be in the hundreds-of-millions category before it becomes problematic. To understand why the probability is so high at such "small" numbers, relatively speaking, you might want to read about the birthday problem.

3
  • 1
    Thanks sfdcfox. The Design in terms of DELETIONS and UNDELTION is already bullet prof. This is ensured by the "ALL ROWS" which includes stuff in the trash. Records which are erased from Recycle can lead to the re-use of their UIN__c - which is acceptable by design. We need to ensure only UNIQUENESS at one point in time. We do not want the overhead of custom settings. The mechanism must be minimal invasive without having any additional footprint than the UIN__c custom field. The trigger checks its existence and acts dynamically, if existent.
    – Uwe Heim
    Oct 1, 2021 at 13:26
  • 1
    so we really are looking for a mechanism to get MAX( UIN__c ) programmatically which works for any given objects without flaws regardless of the record count. Is there really NO way to get MAX() on large objects to work with a trick? Or is the "selective" requirement in deed the show stopper for any thinkable bullet proof MAX() approach on huge record counts?
    – Uwe Heim
    Oct 1, 2021 at 13:28
  • 1
    @UweHeim What about marking the field as External Id, then? Using Order By Desc with a limit should give you correct results, but keep in mind you'll still need a locking statement for concurrent records, so it doesn't matter much if you use the field on the record or a custom setting.
    – sfdcfox
    Oct 1, 2021 at 14:46

You must log in to answer this question.

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