function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
DaveGraupnerDaveGraupner 

Governor Limits and Count()

 

Is there any way to do a Count() query without hitting governor limits and without using batch apex when the number of records to be counted is greater than the governor limit?

 

 

Integer affected = [SELECT COUNT() FROM Account WHERE CustomField__c = 'TestValue'];

 This code runs in a trigger so will fail if there are more than 100 records in the count (assuming a batch size of 1).

 

 

Thanks

 

Dave

Best Answer chosen by Admin (Salesforce Developers) 
SargeSarge

 

      I would suggest you to rethink on your design. There is no way you can get away for governor limits if you are deciding to count the records whenever you are performing an DML operation. You might solve the problem in a way to get around it but as days passes and records grow in future, you would certainly reconsider your code during that time.

 

Hence better change the design if it is not too late now.

 

Quick option:

 

      If you think there are not more than 500 records to count and your batch size is always 1 you can try this algoritm:

Again this is under governor limits and consumes more resources:

 

 

integer batchSize = trigger.new.size();
//query rows returned in triggers depend on batch size
List<Id> consideredIds = new List<Id>();

integer count = 0; // variable to hold the count of records
integer appliedLimit= batchSize * 100;
//documentation suggests batchsize * 1000 but keep 100 for safer side
List<Account> currentAccounts;

for(integer i=0; i< 5;i++){


currentAccounts = new List<Account>();
//as loop progresses records returned in previous iteration are rejected by filter
currentAccounts = [SELECT Id
FROM Account
WHERE Custom_Field__c = 'TestValue'
AND
Id NOT IN :consideredIds
LIMIT :appliedLimit
];

if(currentAccounts.size() == 0) {

break;

}else{

count += currentAccounts.size();

for(Account a: currentAccounts){

consideredIds.add(a.Id);

}//inner for loop ends

}

}//for loop ends

 

 

I truley understand this is a dirty trick to count records but if you have run out of options you can give a try.

 

Risks involved in this:

1) Since SOQL query is inside for loop, and executes at max 5 times, a lot of system resources might get consumed when you do unit testing all classes(Test All)

2) if >500 records to count then you might  rethink to increase the for loop iteration. Here a balance between resources and records is in question, which may seem unstable as records grow.

3) You can say more looking at your solution design.......

As always , to count records, use a asynchrounous execution like batch or @future annotion.

 

Cheers..

 

All Answers

imuino2imuino2

Why do you say the limit is ten?

You will be able to count 10000 fields.

I did test it in my eclipse (execute anonimous) with an object with lots of fields and it didn't hit the limit.

What you can do is to test the query in eclipse on execute anonimous Window -> Show view -> Execute anonimous.

 

Ignacio

DaveGraupnerDaveGraupner

Hi Ignacio,

 

I am counting records and not fields.

If you exectute the following in the system console

 

Integer count = [SELECT COUNT() FROM Opportunity];

 

you will get an error if you have more than 10,000 opportunities as the governor limit for rows returned is 10,000 in anonymous blocks. The error is: System.Exception: Too many query rows: 10001


The limit is 100 (for batch size of 1) when trigger code is executing.

 

So original question still stands how to count records if more than 100 or 10,000?

 

Thanks

 

Dave

 

 

SargeSarge

 

      I would suggest you to rethink on your design. There is no way you can get away for governor limits if you are deciding to count the records whenever you are performing an DML operation. You might solve the problem in a way to get around it but as days passes and records grow in future, you would certainly reconsider your code during that time.

 

Hence better change the design if it is not too late now.

 

Quick option:

 

      If you think there are not more than 500 records to count and your batch size is always 1 you can try this algoritm:

Again this is under governor limits and consumes more resources:

 

 

integer batchSize = trigger.new.size();
//query rows returned in triggers depend on batch size
List<Id> consideredIds = new List<Id>();

integer count = 0; // variable to hold the count of records
integer appliedLimit= batchSize * 100;
//documentation suggests batchsize * 1000 but keep 100 for safer side
List<Account> currentAccounts;

for(integer i=0; i< 5;i++){


currentAccounts = new List<Account>();
//as loop progresses records returned in previous iteration are rejected by filter
currentAccounts = [SELECT Id
FROM Account
WHERE Custom_Field__c = 'TestValue'
AND
Id NOT IN :consideredIds
LIMIT :appliedLimit
];

if(currentAccounts.size() == 0) {

break;

}else{

count += currentAccounts.size();

for(Account a: currentAccounts){

consideredIds.add(a.Id);

}//inner for loop ends

}

}//for loop ends

 

 

I truley understand this is a dirty trick to count records but if you have run out of options you can give a try.

 

Risks involved in this:

1) Since SOQL query is inside for loop, and executes at max 5 times, a lot of system resources might get consumed when you do unit testing all classes(Test All)

2) if >500 records to count then you might  rethink to increase the for loop iteration. Here a balance between resources and records is in question, which may seem unstable as records grow.

3) You can say more looking at your solution design.......

As always , to count records, use a asynchrounous execution like batch or @future annotion.

 

Cheers..

 

This was selected as the best answer
DaveGraupnerDaveGraupner

Thanks for the suggestions Sarge,

 

I was using the count query to decide whether to process records inside a trigger or execute in batch mode. In most cases the number of records is small so code inside the trigger works fine. Only about 1% or less of the updates need to be prcoessed in batch.

 

If I add the LIMIT batchSize * 100 clause to my count query then it will always work and if the returned count is less than the LIMIT then I can process inside the trigger otherwise in batch. Obviously I'll need to tweak the actual LIMIt to take into account other prcoessing that is being done inside the trigger.

 

As you said rethinking the design is the better way!

 

Regards

 

Dave

SargeSarge

Dave,

 

    Decision to process inside a trigger or batch is really a good trick. Good luck with that and happy coding.

 

Cheers....