function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Maria AlberiMaria Alberi 

Too many query rows

Hi all,

I've got a Too many query rows problem in a class.

I've got a custom object, call Prodotti_promozioni_commerciali__c, from I want to create a custom dashboard. This object is a sort of "Bridge object" and has a relationship with Prodotto__c object, who has a Famiglia_dsh__c and a Set_Prod_dsh__c fields, and a relationship with account, who has a ACCOUNTCATEGORY_MI__c fields. It is correlated to another custom object (Promozione_commerciale__c) too. The idea is this: I select from a wizard a family, and I can see for every family the percentage of account related with a promotion (represented with Prodotti_promozioni_commerciali__c object) between total accounts . Every family has 3 or 4 sets correlated to it, and every account can have one between four category: A, B, C,D. The dashboard is composed by lines and columns, the lines represent the set of products for each family, the columns represent possible account category. I have also a datetime picker in order to specify the dates range in which the Prodotti_promozioni_commerciali__c must be shown, depending of Data_adesione__c field of Promozione_commerciale__c object. I have a Bonus picklist Yes/no/all too, if I select Yes, I should see only records with Bonus__c checkbox field = true, same thing if select No, while if I select all I should see both records.

In my code I make a for loop for each product set, like this: in this case I consider only A category, for B,C,D category code is similar.

 

for (string selectedSetProd: listasetProdotti) {

listapromcommA = new set<string>();
List<Prodotti_promozioni_commerciali__c> listaaccounttotA = new List<Prodotti_promozioni_commerciali__c>();
if(selectedBonus == 'all'){
listaaccounttotA = [select Promozione_commerciale__r.Referente__r.AccountId,
Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c,
Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c,
Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName,
Promozione_commerciale__r.Referente__r.Account.Form_Acc_Cat__c from
Prodotti_promozioni_commerciali__c where Promozione_commerciale__r.Referente__r.Account.Ownerid in:utentiriferimento
AND Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c = 'Attivo'
AND Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName = 'Ambulatorio'
AND Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c = 'A'
AND Promozione_commerciale__r.Referente__r.Account.ABILITATED_MI__c = true
AND Promozione_commerciale__r.Referente__r.Account.CHECKACCOUNT_MI__c = true
AND Prodotto__r.Famiglia_dsh__c =:selectedFamilyNew
AND Prodotto__r.Set_Prod_dsh__c =:selectedSetProd
AND Promozione_commerciale__r.Data_adesione__c >=: datainizio
AND Promozione_commerciale__r.Data_adesione__c <=: datafine
AND Promozione_commerciale__r.Stato__c = 'Approvata'
];

}
else
{
listaaccounttotA = [select Promozione_commerciale__r.Referente__r.AccountId,
Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c,
Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c,
Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName,
Promozione_commerciale__r.Referente__r.Account.Form_Acc_Cat__c from
Prodotti_promozioni_commerciali__c where Promozione_commerciale__r.Referente__r.Account.Ownerid in:utentiriferimento
AND Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c = 'Attivo'
AND Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName = 'Ambulatorio'
AND Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c = 'A'
AND Promozione_commerciale__r.Referente__r.Account.ABILITATED_MI__c = true
AND Promozione_commerciale__r.Referente__r.Account.CHECKACCOUNT_MI__c = true
AND Prodotto__r.Famiglia_dsh__c =:selectedFamilyNew
AND Prodotto__r.Set_Prod_dsh__c =:selectedSetProd
AND Promozione_commerciale__r.Data_adesione__c >=: datainizio
AND Promozione_commerciale__r.Data_adesione__c <=: datafine
AND Promozione_commerciale__r.Stato__c = 'Approvata'
AND Bonus__c =: selectedBonusBool];
}

for(Prodotti_promozioni_commerciali__c ppc:listaaccounttotA){
listapromcommA.add(ppc.Promozione_commerciale__r.Referente__r.AccountId);
}

List<AggregateResult> countaccountpromoAntintA = [SELECT count(Id) conteggiopromo
FROM Account
WHERE Id in: listapromcommA
];

 if(listaaccounttotA.size()> 0) {
DHW_Merial_2__c dmerial = new DHW_Merial_2__c();
dmerial.name = 'prova';
if(listaaccounttotA.size()> 0){
dmerial.Form_Account_accountStatus__c = listaaccounttotA.get(0).Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c;
dmerial.Form_ProdottiPromozioniComm_Prodotto_Fam__c = selectedFamilyNew;
dmerial.Form_ProdottiPromozioniComm_Prodotto_Set__c = selectedSetProd;
dmerial.Form_Account_Category__c = listaaccounttotA.get(0).Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c;
dmerial.Form_Account_RecordTypeId__c = listaaccounttotA.get(0).Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName;
}

if( String.valueOf(countaccountpromoAntintA.get(0).get('conteggiopromo')) == null || String.valueOf(countaccountpromoAntintA.get(0).get('conteggiopromo')) == 'NaN'
|| countaccountpromoAntintA.get(0).get('conteggiopromo') == '0'
|| countaccountpromoAntintA.get(0).get('conteggiopromo') == 0){

 

dmerial.Count_account_promo__c = 0;
}
else
{
dmerial.Count_account_promo__c = Integer.valueOf(countaccountpromoAntintA.get(0).get('conteggiopromo'));
}

if( String.valueOf(countaccountotaleA.get(0).get('conteggiotot')) == null || String.valueOf(countaccountotaleA.get(0).get('conteggiotot')) == 'NaN' ){
dmerial.Count_account__c = 0;
}
else
{
dmerial.Count_account__c = Integer.valueOf(countaccountotaleA.get(0).get('conteggiotot'));
}
dmerial.name = 'testdmerial'+ Date.today();
insert dmerial;

List<DHW_Merial_2__c> numero = [select Count_account_percent__c from DHW_Merial_2__c where id =: dmerial.id limit 1];

if(numero.size()> 0){
numeropercent = numero.get(0).Count_account_percent__c;

}
acccounta = dmerial.Count_account__c;


if(conteggioA == 0){
percentuale1a = numeropercent;
accpromo1a = dmerial.Count_account_promo__c;
}
else if(conteggioA == 1){
percentuale2a = numeropercent;
accpromo2a = dmerial.Count_account_promo__c;
}
else if(conteggioA == 2){
percentuale3a = numeropercent;
accpromo3a = dmerial.Count_account_promo__c;
}
else if(conteggioA == 3){

percentuale4a =numeropercent;
system.debug('percentuale 4a debug' + numeropercent);
accpromo4a = dmerial.Count_account_promo__c;

}
conteggioA++;


}
if(acccounta== 0){
formulaaccpromoa = 0;
}
else {



formulaaccpromoa = (100 * (Integer.valueOf(countaccountpromoAntintAtuttiprod.get(0).get('conteggiopromo'))))/acccounta; 

}

 

For a particular family, cause there are several records, more than other families records, I obtain a "Too many query rows", if I check All bonus , and I check a large range of dates. How can I solve this, without riducing number of records obtained with where filters?

Do I have to re-write code?

Thank you in advance for your support

 

Maria

Best Answer chosen by Maria Alberi
Starz26Starz26

You will need to remove the SOQL from the loop as previously suggested or you will run into other issues (this will not solve your current issue).

 

You are simply returning to many results. You need to limit the data returned using LIMIT statements or modifying your queries. Depending on how you are using this data, you may want to use batch apex. Again it all depends on your use case. 

 

You cannot get around this limit, you have to modify your solution to work withint the query row limit of 50,000 records.

All Answers

georggeorg

Take out the query from for loop by using map or other collections. If the problems doesn't get solved try following.

 

1.Make your query selective either making field as external and filter by that field and add few more filters which are unique

2.Use @readonly annotation on that method, but the method should be webservice or a scheduable interface, see documentation for more details.

 

Hope this helps:-)

 

Thanks,

George
Maria AlberiMaria Alberi

Hi George, thank you your queek answer. The problem is that making method "readonly", this must be static. To get method static I need to modify all my code and making all  variables used by that method static! I tried to do this, and nothing works...Can you help me more, please?
Starz26Starz26

You will need to remove the SOQL from the loop as previously suggested or you will run into other issues (this will not solve your current issue).

 

You are simply returning to many results. You need to limit the data returned using LIMIT statements or modifying your queries. Depending on how you are using this data, you may want to use batch apex. Again it all depends on your use case. 

 

You cannot get around this limit, you have to modify your solution to work withint the query row limit of 50,000 records.

This was selected as the best answer
Maria AlberiMaria Alberi

Hi, to solve the too many query rows problem, I am trying to build an apex batch solution like this one. I have got an apex batch bob and  a main class that call the first one.

 

This is the apex job code:


global class GoogleChartBatchAll implements Database.Batchable<sObject>{
global string selectedBonus ;
global string selectedFamilyNew ;
global set<string> utentiriferimento;
global Date datainizio;
global Date datafine;
// global integer conteggioquery;
global string Query = '';
global decimal numeropercent = 0;
global decimal percentualeZero = 0;
global decimal acccount = 0;
List<AggregateResult >countaccountotale;

public GoogleChartBatchAll(){

}
public GoogleChartBatchAll(set<string> utentiriferimento,string selectedBonus,string selectedFamilyNew,Date datainizio,Date datafine,
decimal numeropercent,decimal percentuale0,decimal acccount,List<AggregateResult >countaccountotale){

this.utentiriferimento = utentiriferimento;
this.selectedBonus = selectedBonus;
this.selectedFamilyNew = selectedFamilyNew;
this.datainizio = datainizio;
this.datafine = datafine;
// this.conteggioquery = conteggioquery;
this.numeropercent = numeropercent;
this.acccount= acccount;
percentualeZero = percentuale0;
this.countaccountotale = countaccountotale;
}


global Database.QueryLocator start(Database.BatchableContext BC)
{

string attivo = 'Attivo';
string ambulatorio = 'Ambulatorio';
string approvata = 'Approvata';

List<Prodotti_promozioni_commerciali__c> listaaccounttot = new List<Prodotti_promozioni_commerciali__c>();
boolean selectedBonusBool;
string datainizio = string.valueOf(datainizio);
string datafine = string.valueOf(datafine);
if(selectedBonus == 'true'){
selectedBonusBool = true;
}
else
{
selectedBonusBool = false;
}
Query = 'select Promozione_commerciale__r.Referente__r.AccountId,Prodotto__r.Famiglia_dsh__c, Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c,Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c,Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName,Promozione_commerciale__r.Referente__r.Account.Form_Acc_Cat__c from Prodotti_promozioni_commerciali__c where Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c = \'' + attivo + '\' AND Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName = \'' + ambulatorio + '\'AND Promozione_commerciale__r.Referente__r.Account.ABILITATED_MI__c = true AND Promozione_commerciale__r.Referente__r.Account.CHECKACCOUNT_MI__c = true AND Prodotto__r.Famiglia_dsh__c = \'' + selectedFamilyNew + '\' AND Promozione_commerciale__r.Data_adesione__c >= ' + datainizio.substring(0,10) + ' AND Promozione_commerciale__r.Data_adesione__c <= ' + datafine.substring(0,10) + ' AND Promozione_commerciale__r.Stato__c = \'' + approvata + '\'';


query = 'select Promozione_commerciale__r.Referente__r.AccountId,Prodotto__r.Famiglia_dsh__c, Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c,Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c,Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName,Promozione_commerciale__r.Referente__r.Account.Form_Acc_Cat__c from Prodotti_promozioni_commerciali__c where Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c = \'' + attivo + '\' AND Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName = \'' + ambulatorio + '\' AND Promozione_commerciale__r.Referente__r.Account.ABILITATED_MI__c = true AND Promozione_commerciale__r.Referente__r.Account.CHECKACCOUNT_MI__c = true AND Prodotto__r.Famiglia_dsh__c =\'' + selectedFamilyNew + '\'';
system.debug('query ottenuta not all' + query);
return Database.getQueryLocator(Query);
}

 

 

global void execute(Database.BatchableContext BC, List<sobject> scope)
{
system.debug('inside execute');
set<string> listapromcomm = new set<string>();
for(sobject scp:scope){
Prodotti_promozioni_commerciali__c ppc = (Prodotti_promozioni_commerciali__c) scp;
listapromcomm.add(ppc.Promozione_commerciale__r.Referente__r.AccountId);
}

List<AggregateResult> countaccountpromo = [SELECT count(Id) conteggiopromo
FROM Account
WHERE Id in: listapromcomm
];

DHW_Merial_2__c dmerial0 = new DHW_Merial_2__c();
dmerial0.name = 'prova';
if(scope.size()> 0){
dmerial0.Form_Account_accountStatus__c = ((Prodotti_promozioni_commerciali__c)scope.get(0)).Promozione_commerciale__r.Referente__r.Account.ACCOUNTSTATUS_MI__c;
dmerial0.Form_ProdottiPromozioniComm_Prodotto_Fam__c = ((Prodotti_promozioni_commerciali__c)scope.get(0)).Prodotto__r.Famiglia_dsh__c;

// dmerial0.Form_Account_Category__c = listaaccounttot.get(0).Promozione_commerciale__r.Referente__r.Account.ACCOUNTCATEGORY_MI__c;
dmerial0.Form_Account_RecordTypeId__c = ((Prodotti_promozioni_commerciali__c)scope.get(0)).Promozione_commerciale__r.Referente__r.Account.RecordType.DeveloperName;
}


if( String.valueOf(countaccountpromo.get(0).get('conteggiopromo')) == null || String.valueOf(countaccountpromo.get(0).get('conteggiopromo')) == 'NaN' ){
dmerial0.Count_account_promo__c = 0;
}
else
{
dmerial0.Count_account_promo__c = Integer.valueOf(countaccountpromo.get(0).get('conteggiopromo'));
}
system.debug('countaccountotale' + countaccountotale);
if( String.valueOf(countaccountotale.get(0).get('conteggiotot')) == null || String.valueOf(countaccountotale.get(0).get('conteggiotot')) == 'NaN'
|| Integer.valueOf(countaccountotale.get(0).get('conteggiotot')) == 0 ){
dmerial0.Count_account__c = 1; 
}
else
{
dmerial0.Count_account__c = Integer.valueOf(countaccountotale.get(0).get('conteggiotot'));
}
dmerial0.name = 'testdmerial'+ Date.today();
insert dmerial0;

List<DHW_Merial_2__c> numero0 = [select Count_account_percent__c from DHW_Merial_2__c where id =: dmerial0.id limit 1];

system.debug('dentro execute 2' + numero0);

if(numero0.size()> 0){
numeropercent = numero0.get(0).Count_account_percent__c;
}
system.debug('numeropercent' + numeropercent);
percentualeZero = numeropercent ;
system.debug('percentuale0' + percentualeZero);
//HERE "percentualeZero" is NOT null, it has a value (*)
acccount = dmerial0.Count_account__c;
}



global void finish(Database.BatchableContext BC)
{

}
}



 

 

and this is the code (in main class) where I call my batch job:

 

GoogleChartBatchAll batch = new GoogleChartBatchAll(utentiriferimento,selectedBonus,selectedFamilyNew,datainizio,datafine,numeropercent,
percentuale0,acccount,countaccountotale );
Database.executeBatch(batch);
percentuale0 = batch.percentualeZero;
acccount = batch.acccount;  //here "percentualeZero" IS NULL!! (#)

 

The first problem that block me is that I need to return percentuale0 value, but it has a null value in (#) ! What is the problem of my code? At the end of scheduled job, as I specify in (*) it has a value NOT null!

Can you please help me?

 

Maria