batch apex query - ORDER BY doesn't sort all records?

Question

I have a Batch Apex process that queries about 12,000 Contacts - sorted (descending) by a number field. My process ranks these records based on the sort order (record with the highest value in the sorted number field gets a rank of 1, record with the second highest value gets a rank of 2, etc etc). However when reviewing the results, there were incorrect rankings.

I've done a lot of logging/debugging to determine what is going wrong. It appears that the 12,000 Contacts aren't queried as a single group. Instead, I show that 10,000 (exactly) records are sorted correctly and processed in the correct order. And that the other 2,000 records are sorted correctly and processed in the correct order. BUT, if I put the two lists together, the list of 12,000 is not sorted correctly. Said another way, you would think that the final item in the list of 10,000 would be larger than the first item in the list of 2,000, but this is not the case. It's as if the query takes the first 10,000 Contacts it finds, sorts them, and provides them for processing, and then takes the other 2,000, sorts them, and provides them for processing.

Can you provide some insight here? Is it not valid to sort an entire recordset in a batch query? Or is there a way to do it?

Thanks Chris

Answer 1

Thanks for your responses. I opened a Case with SFDC Support and I learned what the problem is.

Restating the issue: Suppose I have 30,000 Contacts and I want to rank them by a certain field in a batch process. My thought was to start the batch process by querying the 30k records, using the ORDER BY clause to sort them. Then I could just iterate over the Contacts and rank them.

However, the response from sfdc is (as I read it) that you won't necessarily receive batches in the exact order in which they appear in the complete result set. Their full response is below. I'd have to do a bit more experimenting to determine exactly what they're doing on the back-end.

Response From Support:

"Batches of records are not guaranteed to execute in the order they are received from the start method."

When we slice the records into batches, the order in which it is processed is not guaranteed. So, when you pass the records to the execute in an order, and when it is divided into multiple batches, salesforce do not process the records in the same order.

So this is an expected behavior.

I hope this answers your question, please let me know if there is any thing else I might help you with.

Answer 2

You might try using the Database.Stateful interface to cache records (or a Map of ID to the Number field value) between (sub)-batches. Then when all batches have run, perform a sort of the records. However, then, you might have trouble saving all the records in one go. To solve that, you could store a Map of record ID to rank. Then start another batch job that re-queries for all records and uses the Map to assign the rank to each record it processes. With the latter batch job, sorting won't be necessary.