Apex Code Development You need to sign in to do that
Don't have an account?
lvivanin 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.
Read this page: Governor limits
It talks about governor limits for triggers.
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.
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.
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!
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.)
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