Getting "Too Many Query Rows" Error on my Trigger

Question

I am getting this error message Too many query rows 500001. My trigger creates a task each time an email message is sent with a WhoID based on the personal or work email fields of a contact, are there any steps I can take to lock it down.

Hope it helps,

trigger EmailToTask on EmailMessage (after insert) {
list<Task> lstTask          = new list<Task>();
map<String,Id> mapEmail     = new map<String,Id>();

for(Contact con: [SELECT Id,Email,npe01__WorkEmail__c,npe01__HomeEmail__c FROM Contact]){
    if(string.isNotBlank(con.Email))
        mapEmail.put(con.Email,con.Id);
    if(string.isNotBlank(con.npe01__WorkEmail__c))
        mapEmail.put(con.npe01__WorkEmail__c,con.Id);
    if(string.isNotBlank(con.npe01__HomeEmail__c))
  mapEmail.put(con.npe01__HomeEmail__c,con.Id);
}
 for (EmailMessage e : Trigger.new) {
   if (e.RelatedToId != NULL 
 && (mapEmail <> NULL && !mapEmail.isEmpty())
      ) {
       Task t =  new Task();
       t.ActivityDate  = date.today();
       t.Description   = e.TextBody;
       t.Move_Type__c  = 'Personal Note/Email from Donor';
       t.Priority      = 'Normal';
       t.Status        = 'Completed';
       t.Subject       = e.Subject;
       t.WhatId        = e.RelatedToId;
       if(mapEmail.containsKey(e.ToAddress)){
            t.WhoId = mapEmail.get(e.ToAddress);
       }     
       lstTask.add(t);
    }
}
if(lstTask <> NULL && !lstTask.isEmpty()){
    insert lstTask;
}}

Answer 1

If you want to query more than 50000 records then you need to use the following syntax:

for(List<Contact> contactsList: [SELECT Id,Email,npe01__WorkEmail__c,npe01__HomeEmail__c FROM Contact]){
  for(Contact con: contactsList){
    if(string.isNotBlank(con.Email))
      mapEmail.put(con.Email,con.Id);
    if(string.isNotBlank(con.npe01__WorkEmail__c))
      mapEmail.put(con.npe01__WorkEmail__c,con.Id);
    if(string.isNotBlank(con.npe01__HomeEmail__c))
      mapEmail.put(con.npe01__HomeEmail__c,con.Id);
  }
}

Answer 2

One way you can reduce your query rows is to apply the filters you are testing for in the first for loop.

However, you want to avoid field != null queries as they are very expensive and result in a full table scan.

One thing you can do is make three calculation fields on your contact object

eg Work_Email_Not_Blank__c and define it something like:

if (isBlank(con.npe01__WorkEmail__c),FALSE,TRUE)

THEN you need to use a workflow or process to copy these fields into static versions eg Work_Email_Not_Blank__c -> Work_Email_Not_Blank_Static__c

Then you can change your top query to something like:

SELECT Id,Email,npe01__WorkEmail__c,npe01__HomeEmail__c 
FROM Contact
WHERE Work_Email_Not_Blank_Static__c = true 
  AND Work_Email_Not_Blank_Static__c
  AND Work_Email_Not_Blank_Static__c