I am writing a trigger for when a Lead is inserted, I want to query all of our org's Contacts to ensure that the Lead's email does not already exist under a Contact. I am using:

List<Contact> allContacts = [select id, email from Contact];

My fear is that we have 600,000+ Contacts in the org, is this going to cause alot of loading time or slow down user's systems at all? Are there any preferred methods to the query I used above?

  • With a number of contacts that large, I think a trigger might not be the best way to go. You could do your call asynchronously in a helper class and call that from the trigger. A better approach might be to use Batch Apex for this. Its really a matter of whether you truly need it in Real Time or can wait for periodic reports that are hourly, daily, etc. – Chris Duncombe Jun 25 '14 at 16:55
  • Real time would be best. But I am going to try out the trigger method in the full sandbox first to see how it goes, if nothing doing then yeah, thanks, I'll probably go with Batch. – JayDP123 Jun 25 '14 at 17:21
  • FYI, you should bear in mind that the consequence of trying to access too much data is not only a slowing down but a dead stop with an uncatchable exception thrown. So you always need to think about the governor limits e.g. a maximum of 10,000 rows retrieved. – Keith C Jun 25 '14 at 17:30

If you want to check Contacts that may have the same Email of the Lead then add a WHERE clause that searches for Contact Email

Map<String, Lead> emailLeadMap = new Map<String, Lead>();
for(Lead l : trigger.new){
    emailLeadMap.put( l.Email, l );
List<Contact> contactsWithMatchingEmail = [SELECT Id, Email FROM Contact WHERE Email IN :emailLeadMap.keySet()];

//Processing here.

This will get you all Contacts that match your Lead email. With the Map you can retrieve your Lead record if necessary to do you processing.

  • Thanks, this will work better I'll go with this for now and test. Thanks for editing too as I was getting an "Initial term of field expression must be a concrete blah blah blah" – JayDP123 Jun 25 '14 at 17:24

If anybody ever is curious about this, I tested out in full Sandbox and got this error when trying to insert a lead:

Error: Invalid Data. Review all error messages below to correct your data. Apex trigger leadFilter caused an unexpected exception, contact your administrator: leadFilter: execution of AfterInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times):

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.