+ Start a Discussion
lvivaninlvivanin 

Best Practice #6: Querying Large Data Sets

I am trying to follow the link

 

 Best Practice #6: Querying Large Data Sets states:

 

SOQL queries that return multiple records can only be used if the query results do not exceed 1,000 records, the maximum size limit of a list. If the query results return more than 1,000 records, then a SOQL query for loop must be used instead, since it can process multiple batches of records through the use of internal calls to query and queryMore.

For example, if the results are too large, the syntax below causes a runtime exception:

//A runtime exception is thrown if this query returns 1001 or more records.
Account[] accts = [SELECT id FROM account];

Instead, use a SOQL query for loop as in one of the following examples:

// Use this format for efficiency if you are executing DML statements 
// within the for loop
for (List<Account> accts : [SELECT id, name FROM account
WHERE name LIKE 'Acme']) {
// Your code here
update accts;
}

Let the Force.com platform chunk your large query results into batches of 1000 records by using this syntax where the SOQL query is in the for loop definition, and then handle the individual datasets in the for loop logic.

 

 --------------------------------------------------------------------------------------------------------------------

 

I have a test object with more than 1000 test records on it.  The test trigger is

 

trigger testProfileEffect on ProfileBasedTest__c (before insert)
{
for(List<ProfileBasedTest__c> pbt:[SELECT description__c FROM ProfileBasedTest__c])
{
pbt[0].description__c = 'Hello world!';
update pbt;
}


}

 

As a test, i am trying to update the existing record's description fields while inserting a new record & get exception:

 

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger Test.testProfileEffect caused an unexpected exception, contact your administrator: Test.testProfileEffect: execution of BeforeInsert caused by: System.Exception: Too many query rows: 1001: Trigger.Test.testProfileEffect: line 3, column 38

 

 

 The exception is because of List limitation but how can I make the Best Practice #6: Querying Large Data Sets workable in this scenerio?

 

Thanks in advance.

 

 

 

Venkat PolisettVenkat Polisett

Read this page: Governor limits

 

It talks about governor limits for triggers.

 

 

 

lvivaninlvivanin

Thank you Venkat Polisett.

 

I know the governor limits for triggers. I intentionally created this scenerio to learn/know for a way out of the problem. 

Venkat PolisettVenkat Polisett

Well, as you already know the governor limit, you cannot process large datasets that are larger than 1000 records from a trigger.

 

Having that ruled out, you can use Web services api to do the udpates outside of the trigger or use batch apex when it becomes available (currently in pilot).

 

Hope this helps.

melchisholm22melchisholm22

Is there any work around in avoiding to hit the governor limits (Too many query rows) in triggers? It should be done in a trigger no where else. I really can't limit the query results to just 1000 since it depend on how many results my query fetches. 

 

Thanks! 

fgwarbfgwarb

I've addressed an issue similar to this by using an Apex Batch job.  The trigger lives on the object and if certain criteria are met then it kicks off the Batch.

 

The batch does all of the heavy lifting querying / updating in governor approved chunks (the size of those chunks are up to you, no more than 200 records at a time.)

manu manu 23manu manu 23
HI, 
To avoid this DML exceeded queries, please create a list and add the accts to the list and then perform DML operations like below 

list<account> updatedAccounts =new list<account>();
for (List<Account> accts : [SELECT id, name FROM account WHERE name LIKE 'Acme']) {
// Your code here
updatedAccounts.add(accts); 
}
update updatedAccounts; //where the dml operation performs only once which avoids the governor limits errors 
Abhinandan Das 13Abhinandan Das 13
I had to use addAll to add the list. Then only it worked.