Query highest value of custom Field on Large Object

Question

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.

Answer 1

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.