+ Start a Discussion
naveen1685naveen1685 

System.LimitException: Too many query rows: 50001

I am getting this exception even after I am providing LIMIT of 5000 to the following dynamic SOQL query:

 

strCamMemQuery = 'SELECT Id,Name,LastName,Initials__c,AccountId,Email ';
strQuery=' FROM Contact WHERE Id NOT IN :existingCampaignMembers ';
strQuery+='AND AccountId IN:acts ';

strCamMemQuery += ' LIMIT 5000';

 

List<Contact> cl = Database.query(strCamMemQuery);

 

Please let me know what is the causing the error.

 

souvik9086souvik9086

Can you share your code here?

May be something causing in the code later than that

 

Thanks

naveen1685naveen1685

The code is very long. I will post only the method. Hope this helps:

 

public void SearchedContacts(){
if (filterAccountType == null) {
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.ERROR, LABEL.Account_Type_Mandatory));
} else if(filterCropInterest<> null && filterCropInterest.size()>0 && (filterCropsCon == null || filterCropsCon.size()==0)){
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.ERROR, LABEL.Select_Crop));
}else{
//resetPaging();
contactsList.clear();
acts.clear();
noContactsFound = false;

strAccQuery = 'SELECT id FROM Account WHERE RecordTypeId = \'' + filterAccountType + '\' ';

if (accFilter.SubType__c != null) {
strAccQuery += ' AND SubType__c = \'' + accFilter.SubType__c + '\' ';
}

if (accFilter.NordicSubtype__c!= null) {
strAccQuery += ' AND NordicSubtype__c = \'' + accFilter.NordicSubtype__c + '\' ';
}

if (accFilter.Base__Country__c !=null){
strAccQuery += ' AND Base__Country__c = \'' + accFilter.Base__Country__c +'\'';
}

if (accFilter.Language__c !=null){
strAccQuery += ' AND Language__c = \'' + accFilter.Language__c +'\'';
}

if(accFilter.Behavioral_CP__c !=null){
strAccQuery += ' AND Behavioral_CP__c = \'' + accFilter.Behavioral_CP__c +'\'';
}

if(accFilter.Behavioural_vegetable_seeds__c !=null){
strAccQuery += ' AND Behavioural_vegetable_seeds__c = \'' + accFilter.Behavioural_vegetable_seeds__c +'\'';
}

if(accFilter.Behavioral_Segment_Arable_Seeds__c !=null){
strAccQuery += ' AND Behavioral_Segment_Arable_Seeds__c = \'' + accFilter.Behavioral_Segment_Arable_Seeds__c +'\'';
}

if(accFilter.Behavioral_arable_seeds_2__c !=null){
strAccQuery += ' AND Behavioral_arable_seeds_2__c = \'' + accFilter.Behavioral_arable_seeds_2__c +'\'';
}

if(accFilter.Customer_Class_Arable_Seeds__c !=null){
strAccQuery += ' AND Customer_Class_Arable_Seeds__c = \'' + accFilter.Customer_Class_Arable_Seeds__c +'\'';
}

if(accFilter.Segmentation_arable_seeds__c !=null){
strAccQuery += ' AND Segmentation_arable_seeds__c = \'' + accFilter.Segmentation_arable_seeds__c +'\'';
}

if(accFilter.Company_Segmentation_CP__c !=null){
strAccQuery += ' AND Company_Segmentation_CP__c = \'' + accFilter.Company_Segmentation_CP__c +'\'';
}

Boolean checkPartner = false;
set<Id> accountIdsFromPartner = new set<Id>();
if(conFilter.AccountId !=null){
for (Partner__c par : [SELECT Account_Name__c
FROM Partner__c
WHERE Partner_Name__c = :conFilter.AccountId]) {
accountIdsFromPartner.add(par.Account_Name__c);
}
checkPartner = true;
}

Boolean checkAttribute = false;
set<Id> accountIdsFromAttribute = new set<Id>();
if(attFilter.Attribute_Name__c !=null){
strattquery = ' SELECT Account__c FROM Attribute__c WHERE Attribute_Name__c = \''+ attFilter.Attribute_Name__c +'\' ';
if(attFilter.Attribute_Value__c !=null){
String str = '';
for (String s : attFilter.Attribute_Value__c.split(';',0)){
str += '\'' + s + '\',';
}
str = str.substring (0,str.length() -1);
strattquery += ' AND Attribute_Value__c includes (' + str + ') ';

}
for (Attribute__c att : Database.query(strattquery)) {
accountIdsFromAttribute.add(att.Account__c);
}
checkAttribute = true;
}

if (filterTopGrowerOnly <> null && filterTopGrowerOnly){
strAccQuery += ' AND Top_Grower__c = True ';
}

if (filterTopGrowerOnly <> null && filterf2fOnly){
strAccQuery += ' AND F2F__c = True ';
}

System.debug('>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>' + strAccQuery);

Boolean checkCropCriteria = false;
Boolean checkProductCriteria = false;
set<Id> accountIdsFromProducts = new set<Id>();
set<Id> accountIdsFromCrops = new set<Id>();
boolean checkValueSegmentation = false;
set<Id> accountIdsFromValue = new set<Id>();
//set<Id> accountIdsFromCropFarmSize = new set<Id>();
if (filterAccountType == GrowerRtId) {

if (filterYear != null) {
strCropQuery = 'Select Cropping__Account_Annual_Info__r.Cropping__Account__c from Cropping__Crop_Record__c ' +
'Where Cropping__Account_Annual_Info__r.Cropping__Year__c = \'' + filterYear + '\' ' ;
Boolean isFirstC = true;

if(filterCrops !=null){
strCropQuery += (isFirstC?' AND ( ': ' Or');
strCropQuery += ' (Cropping__Crop__c = \''+ filterCrops + '\' ';
isFirstC = false;
checkCropCriteria = true;
Integer iCropAreaFrom = filterCropAreaFrom==null?0:filterCropAreaFrom;
Integer iCropAreaTo = (filterCropAreaTo==0 || filterCropAreaTo==null)?99999999:filterCropAreaTo;
strCropQuery = strCropQuery + ' And Crop_Acreage__c >= ' + iCropAreaFrom +
' And Crop_Acreage__c <= ' + iCropAreaTo+') ';
system.debug('@@@cropQuery' + strCropQuery);
}

if(filterCrops1 !=null){
strCropQuery += (isFirstC?' AND ( ': ' Or');
strCropQuery += ' (Cropping__Crop__c = \''+ filterCrops1 + '\' ';
isFirstC = false;
checkCropCriteria = true;
Integer iCropAreaFrom1 = filterCropAreaFrom1==null?0:filterCropAreaFrom1;
Integer iCropAreaTo1 = (filterCropAreaTo1==0 || filterCropAreaTo1==null)?99999999:filterCropAreaTo1;
strCropQuery = strCropQuery + ' And Crop_Acreage__c >= ' + iCropAreaFrom1 +
' And Crop_Acreage__c <= ' + iCropAreaTo1+') ';
system.debug('@@@cropQuery1' + strCropQuery);
}
if(filterCrops2 !=null){
strCropQuery += (isFirstC?' AND ( ': ' Or');
strCropQuery += ' (Cropping__Crop__c = \''+ filterCrops2 + '\' ';
isFirstC = false;
checkCropCriteria = true;
Integer iCropAreaFrom2 = filterCropAreaFrom2==null?0:filterCropAreaFrom2;
Integer iCropAreaTo2 = (filterCropAreaTo2==0 || filterCropAreaTo2==null)?99999999:filterCropAreaTo2;
strCropQuery = strCropQuery + ' And Crop_Acreage__c >= ' + iCropAreaFrom2 +
' And Crop_Acreage__c <= ' + iCropAreaTo2+') ';
system.debug('@@@cropQuery2'+strCropQuery);
}
strCropQuery += ' ) ';
System.debug('@@@strCropQueryFinal ' + strCropQuery);
if(checkCropCriteria){
for(Cropping__Crop_Record__c cr : Database.query(strCropQuery)){
accountIdsFromCrops.add(cr.Cropping__Account_Annual_Info__r.Cropping__Account__c);
}
}
System.debug('@@@accountIdsFromCrops ' + accountIdsFromCrops);

if(filterValueSegmentation !=null){
for(Cropping__Account_Annual_Info__c aai : [ select Cropping__Account__c
from Cropping__Account_Annual_Info__c
WHERE Final_Segmentation__c = :filterValueSegmentation
And Cropping__Year__c = :filterYear ]){
accountIdsFromValue.add(aai.Cropping__Account__c);
}
checkValueSegmentation = true;
}

strProQuery = 'select Opportunity.AccountId FROM OpportunityLineItem ' +
'WHERE oppBase__Opportunity_Name__c = \'' + filterYear + '\' ';
Boolean isFirstP = true;

if (filterOppProducts !=null){
strProQuery += (isFirstP?' AND ( ': ' Or');
strProQuery += ' (PricebookEntry.Product2Id = \'' + filterOppProducts + '\' ';
checkProductCriteria = true;
isFirstP = false;
Integer ifilterQuantityFrom = filterQuantityFrom==null?0:filterQuantityFrom;
Integer ifilterQuantityTo = (filterQuantityTo==0 || filterQuantityTo==null)?99999999:filterQuantityTo;
strProQuery += ' AND oppBase__Quantity_Dup__c >= ' + ifilterQuantityFrom +
' AND oppBase__Quantity_Dup__c <= ' + ifilterQuantityTo +') ';

}

if (filterOppProducts1 !=null){
strProQuery += (isFirstP?' AND ( ': ' Or');
strProQuery += ' (PricebookEntry.Product2Id = \'' + filterOppProducts1 + '\' ';
checkProductCriteria = true;
isFirstP = false;
Integer ifilterQuantityFrom1 = filterQuantityFrom1==null?0:filterQuantityFrom1;
Integer ifilterQuantityTo1 = (filterQuantityTo1==0 || filterQuantityTo1==null)?99999999:filterQuantityTo1;
strProQuery += ' AND oppBase__Quantity_Dup__c >= ' + ifilterQuantityFrom1 +
' AND oppBase__Quantity_Dup__c <= ' + ifilterQuantityTo1 +') ';
}

if (filterOppProducts2 !=null){
strProQuery += (isFirstP?' AND ( ': ' Or');
strProQuery += ' (PricebookEntry.Product2Id = \'' + filterOppProducts2 + '\' ';
checkProductCriteria = true;
isFirstP = false;
Integer ifilterQuantityFrom2 = filterQuantityFrom2==null?0:filterQuantityFrom2;
Integer ifilterQuantityTo2 = (filterQuantityTo2==0 || filterQuantityTo2==null)?99999999:filterQuantityTo2;
strProQuery += ' AND oppBase__Quantity_Dup__c >= ' + ifilterQuantityFrom2 +
' AND oppBase__Quantity_Dup__c <= ' + ifilterQuantityTo2 +') ';
}
strProQuery += ' ) ';
system.debug('@@@strProQuery '+ strProQuery );
if(checkProductCriteria){
for (OpportunityLineItem oli : Database.query(strProQuery)){
accountIdsFromProducts.add(oli.Opportunity.AccountId);
}
}
}
}
if (checkPartner) {
strAccQuery += ' AND Id = :accountIdsFromPartner ';
}

if (checkAttribute){
strAccQuery += ' AND Id = :accountIdsFromAttribute ';
}

if(checkValueSegmentation){
strAccQuery += ' AND Id = :accountIdsFromValue ';
}

if(checkCropCriteria || checkProductCriteria) {

strAccQuery += ' AND (Id = :accountIdsFromCrops OR Id = :accountIdsFromProducts ) ';
}

for(CampaignMember cm : [SELECT ContactId FROM CampaignMember WHERE CampaignId = :strCampaignId])
existingCampaignMembers.add(cm.ContactId);

String strCamMemQuery;
String strCamMemCountQuery;
String strQuery;
String strContactRoleQuery;
String strContactList;

//Querying all the account id's
System.debug('@@@account query'+strAccQuery);
for(Account a:Database.query(strAccQuery)){
acts.add(a.Id);
}
system.debug('@@@Total accounts'+acts.size());

//Query to extract all the contact id's from Contact roles
strContactRoleQuery='Select ContactId FROM AccountContactRole where AccountId IN :acts ';
system.debug('@@@strContactRoleQuery'+strContactRoleQuery);
RoleQuery = strContactRoleQuery;

strCamMemQuery = 'SELECT Id,Name,LastName,Initials__c,AccountId,Email ';
strCamMemCountQuery = 'SELECT count()';
strQuery=' FROM Contact WHERE Id NOT IN :existingCampaignMembers ';
strQuery+='AND AccountId IN:acts ';
//strQuery += 'AND ( AccountId IN :acts ';
//strQuery +='OR Id IN :contacts)';
if(filterContactType!=null){
strQuery += ' AND RecordTypeId = \'' + filterContactType + '\' ';
}
system.debug('@@@strQuery ' + strQuery);

if(filterCropInterest<> null && filterCropInterest.size()>0 && filterCropsCon <> null && filterCropsCon.size()>0){
String strCropIntQuery='';
strCropIntQuery = 'select Contact__c from Crop_Information__c where Crop_Interest__c = :filterCropInterest AND Crop__c = :filterCropsCon ';
strQuery += ' AND Id IN (' + strCropIntQuery + ') ';

}
system.debug('@@@strQueryafter crop interest' + strQuery);
if(filterCommunicationOptOut.size()>0) {
Set<String> coo = new Set<String>();
for(String str : filterCommunicationOptOut)
coo.add(str);
String q='';
if(coo.contains('Memail')) {
q = ' AND Do_not_use_marketing_email__c = false ';
}
if(coo.contains('Pemail')) {
q += ' AND Do_not_use_personal_email__c = false ';
}
if(coo.contains('Sms')){
q += ' AND Do_not_use_SMS__c = false ';
}
if(coo.contains('Phone')) {
q += ' AND Do_not_use_Phone__c = false ';
}
if(coo.contains('DMail')) {
q += ' AND Do_not_use_Direct_Mail__c = false ';
}
strQuery += q;
}
if(filterEmail){
strQuery += ' AND Email != null ';
}
//Adding Phone attribute
if(filterPhone){
strQuery += ' AND MobilePhone != null ';
}

Boolean checkConAttribute = false;
set<Id> contactIdsFromConAttribute = new set<Id>();
if(conattFilter.Contact_Attribute_Name__c !=null ){
strConAttQuery = ' SELECT Contact__c FROM Contact_Attribute__c WHERE Contact_Attribute_Name__c = \''+ conattFilter.Contact_Attribute_Name__c +'\' ';
if(conattFilter.Contact_Attribute_Value__c !=null){
String str = '';
for (String s : conattFilter.Contact_Attribute_Value__c.split(';',0)){
str += '\'' + s + '\',';
}
str = str.substring (0,str.length() -1);
strConAttQuery += ' AND Contact_Attribute_Value__c includes (' + str + ') ';

}
for (Contact_Attribute__c att : Database.query(strConAttQuery)) {
contactIdsFromConAttribute.add(att.Contact__c);
}
checkConAttribute = true;
}

if(checkConAttribute){
strQuery += ' AND Id = :contactIdsFromConAttribute ';
}

if(conFilter.Language__c !=null){
strQuery += ' AND Language__c = \'' + conFilter.Language__c +'\' ';
}

System.debug('@@@strQueryFinal' + strQuery);

strCamMemCountQuery += strQuery + ' limit 5000';
strCamMemQuery += strQuery;
pQuery = strCamMemQuery;
strCamMemQuery += ' LIMIT 5000';

Set<Id> conRoles=new Set<Id>();
for(AccountContactRole c:Database.query(RoleQuery)){
conRoles.add(c.ContactId);
}
List<Contact> ContactFromRoles = [select Id,Name,LastName,Initials__c,AccountId,Email from Contact where Id IN:conRoles];

system.debug('@@@QUERY-----------'+strCamMemQuery);
List<Contact> cl = Database.query(strCamMemQuery);
if(ContactFromRoles.size()>0){
for(Contact cr : ContactFromRoles){
cl.Add(cr);
}
}

if (cl.size() <= 1000)
contactsList = cl;
else {
for (Integer i = 0; i < 1000; i++) {
contactsList.add(cl[i]);
}
}
resultSize = cl.size();
if (resultSize == 0) {
noContactsFound = true;
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.ERROR, 'No contacts found'));
}
system.debug('@@@contact List----------'+contactsList);
}
}

Venkatesh.ax1803Venkatesh.ax1803

The limit of records fetching in context from all SOQL queries is 50000.  It is not for one query, check the size of the records retuned from all the SOQL queries in context, i think that exceeding limit.

naveen1685naveen1685

I am getting the error only in one case, when only recordtype is taken as the filter criteria, for example,

 

SELECT Id,Name,LastName,Initials__c,AccountId,Email FROM Contact WHERE AccountId IN:acts LIMIT 5000

 

Here, acts has all account ids fetched from the user selected record type (say Distributor). Rest all conditions are null and thus, other queries will not be executed.

 

The above mentioned query should return only 5000 rows but this is returning more than 50000 which i don't understand.

 

Please advise.