Querying against a large object

Question

We have a "dirty" leads custom object.

It has around 1000 records. We will be getting similar files which we will load into dirty leads object every once in a while.

Our lead object has around 800k records.

I would like to create a dedup process that checks whether the incoming records in the dirty lead custom objects already exist in Lead.

My problem here is how to design code for this process.

How am I supposed to store the 800k records in memory as I am sure I will hit the heap limit...then what's the best way to achieve this ?

Would appreciate any help on this

Answer 1

Don't read them all in memory, but do specific queries to see if one or more of the 800k record matches the 1000 dirty lead records.

The searching should be in the database, and less in code. What I would do is to make the Database search as specific as possible (not necessarily 100% specific, but the results should be duplicate candidates), and then for the results that you get back, loop over them in code and do the 100% check.

For the database, the LIKE operator might come in useful. See https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm and https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_conditionexpression.htm