May 3, 2010

Using System.runAs() for Too Many Query Rows 501 errors !

System.runAs(<User>) is often ignored by Apex developers for their Apex Test Classes, though its a wonderful unique feature available for Apex test cases. If we use this correctly in our test code, it will for sure reduce the chances of getting “Too many query rows 501 error” on test case execution in any org.

System.Exception: Too many query rows: 501 – Why I GOT THIS ?

On this error, common reactions are

  • What is this too many rows 501 error ?
  • Why I am getting this error now, I did no code changes and my test code was running fine before ?
  • I am not accessing too many rows in my test code, my test data set is limited to 10-20 records at max only.
  • I am just using COUNT() in SOQL, how can I get more than 500 records in that.

501 error comes when we cross the governor limit “Total number of records retrieved by SOQL queries”. As per this limit a single Apex testMethod can only retrieve 500 rows in all SOQL calls. We all should agree that 500 is decently big number for any good test data set. I have never seen any developer creating this big test data in Apex code, we are lazy in this regard and this is boring too sometimes :)

Prime reasons for “TOO MANY QUERY ROWS 501” error

  1. Test code relies on ORG data, so its quite possible that in sandbox or developer env you have limited data. But the same code will fail for this error, if executed in production or any other org having massive user/customer data.
  2. Developer believes that he is only using Aggregate functions like COUNT() that return a single row, hence should be counting for 1 SOQL statement for limits. But the truth is, all records being counted on firing a SOQL having COUNT() function are deducted from your governor limits quota. Try executing this SOQL “[select count() from contact]” in Anonymous block, what number are you expecting for “Number of query rows” LIMIT ? Is that ONE, Nooo its is the total number of records counted. On my org its “Number of query rows: 1392 out of 10000” for this single SOQL query.
  3. Developer creates good test data apart from ORG data. But the code written to be executed in DEFAULT/SYSTEM MODE(The permissions and record sharing of the current user are not taken into account), thus its accessing both the ORG DATA + TEST DATA. Again, You might not see the error if
    • One is running tests in development org, where developers create limited data for Manual testing and Apex Test Class.
    • The application you built, is getting installed for first time. Assuming its not depending on many of Standard Objects and there are no records for newly created custom objects.
    • Developer is using System.runAs() in all test cases, Trust me it will be safe if used correctly :)

How should I get rid of “Too many query rows 501 error” ?

The only key to get rid of this error is is “ISOLATION of ORG DATA from TEST DATA !”. Following are the key points to take care while writing apex test code

  • Always create good test data for each test method, never rely on ORG data. It might not be available on deployments to other orgs.
  • Never drive test in SYSTEM MODE, that is apex default if you don’t use System.runAs(). System mode by passes most of the sharing rules and will make your test code see huge org + test data.

I will not explain creation of good test data, its out of scope for this post. Rest of the post below will explain what is System.runAs() and how to best use it.

What is System.runAs() ?

The system method runAs enables you to write test methods that change either the user contexts to an existing user or a new user, or to run using the code from a specific version of a managed package.When running as a user, all of that user's record sharing is then enforced. You can only use runAs in a test method. The original system context is started again after all runAs test methods complete.

How System.runas() will kick OFF “Too many query rows: 501” ?

The key reason behind this 501 error is SYSTEM MODE, which is default for apex code execution. This mode opens all org data to your test code.So so so your test case is vulnerable to this error in any execution scenario, where the org has too much user data.

System.runAs() is a life saver because, it drives the test code for a User/Profile mode rather SYSTEM MODE, so your test code only sees the data visible to the given user/profile as per records sharing rules. 

Best practice for using System.runAs()

You can still get “TOO MANY QUERY ROWS: 501” error after using System.runAs(). It will for sure happen, if you are depending on any existing user in System, and that user has access to huge org data that is related to your test context. So, the best way to use System.runAs() is to create a brand new User with appropriate Role/Profile, this User object should be used as argument in System.runAs(<Newly Created User>).

Together with System.runAs(), try using Test.startTest() and Test.stopTest() to get some extra bandwidth on governor limits. As shown in code sample below.

Here is a sample of using System.runAs()

public class TestRunAs2 {
    public static testMethod void test2() {
        Profile p = [SELECT Id FROM profile WHERE name='Standard User'];
        User u2 = new User(alias = 'newUser', email='',
        emailencodingkey='UTF-8', lastname='Testing', 
        languagelocalekey='en_US', localesidkey='en_US', profileid = p.Id,
        timezonesidkey='America/Los_Angeles', username='');

        System.runAs(u2) {    
          // The following code runs as user u2.
          // all your test data, created here in
          // this block will not run in system mode. 
          // So access to org level data 
          // will be pretty limited

          // Create all your Test Data + Structures till here
          // use startTest() to give some extra buffer
          // to your real test code
            // Do any extra DML and other stuff  
            // you have more bandwidth now.
        // Any other validation and DML statements
        // if required to validate the results.

Note: In the code sample above “We didn’t inserted the user object, the in-memory user instance is working in System.runAs() perfectly. So we can safely avoid doing such insertions !”