fix a soql query that is hitting governor limits

Question

I have 500 territories to plough through and 500k contacts. I tried a group by, but can't get it to work. How can I avoid running into the governor limit for the number of soql queries and rows returned ?

public with sharing class Test {
  public static void  test() {
    List <_Territory__c> terrsWithAee = [select id,name, Aee_Territory__c from _Territory__c where Aee_Territory__c != null];
    String s = ' t.test Terrs with Aee = ' + terrsWithAee.size() + '\n';

    for (_Territory__c t :terrsWithAee) {
      Integer err = [select count() from contact where (Contact_AML_Territory__c  = :t.id AND BDW_AML_Territory__c != :t.Aee_Territory__c ) OR (Contact_ChoicePlus_Territory__c  = :t.id AND BDW_CP_Territory__c != :t.Aee_Territory__c)]; 

      s = 'For   Terr '+ t.id  + '  errors=  ' + err + ' terr name = \n'  ;
    }
  }
}

Answer 1

There are only two ways to query those 500,000 contact rows.

1. Set this up as a batch job on territories and set the batch size to 1 or some other small number so that you are only looking at the contacts for a few territories at once.

2. Use the @readonly annotation. This will remove query row limitations but you will not be able to perform any DML statements or do anything else that will hit the database.