Press ESC to close

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 !”

Comments (11)

  • Anonymoussays:

    September 6, 2010 at 6:59 am

    I also tired your idea but it fails.Tried by creating a user with Standard User,Standard Platform User, – Free User,System Administrator.When my test gets failed by retrieving 500 records

  • Anonymoussays:

    September 6, 2010 at 7:27 am

    @suresh, System.runAs(user) limits the number of visible records as per parameter user's profile. Seems in your case, all these profiles have access to a big chunk of org data.You can try wrapping System.runAs() in other available options like start/stopTest(). This may give you some more bandwidth to play. Here is a sample// Create Test Data hereTest.startTest(); System.runAs(user) {// All DML/SOQL for Test Code here } Test.stopTest();

  • Anonymoussays:

    September 6, 2010 at 8:13 am

    Hi Gupta.Thanks for your time and quick reply.The issue is created a new user as Standard User.The Standard User profile has access to even when the test code calls the function to query the Account all records(700) are retrieved.So I ran out of Limit even though I used System.runAs(newuser1).Then I added start and stop function it worked fine even though the records retrieved was 700.At last I went with above said code the records retrieved is same 700 not the record which I inserted during testing.So System.runAs() is not useful even though the new user is created as Standard User.

  • Anonymoussays:

    September 14, 2010 at 11:07 pm

    Thanks for this very helpful post. Two questions…1) Is there a way to create a testing user that doesn't consume an active user license, which costs us $1,800 per year?2) My organization is not restricting access to any records by organization wide defaults. We are a small business and everyone in the org can see and edit every contact/account. We'd like to keep it that way. Given that, it seems that RunAs can't solve our problem because we'd first have to change our sharing model in order to restrict the test user's access to records. Correct?Given those things, is there anything else we can do to resolve the 501 error? We are already putting our DML operations inside startTest and stopTest.Any help much appreciated!

  • Anonymoussays:

    September 16, 2010 at 2:29 am

    @Sam thanks for appreciation, glad you find this helpful.Here are my views on your queries.1) In the sample code shown with the blog post, I am not creating a new User by “insert userSObject”. We are just creating a user on the fly/in-memory for the test run. for ex. User u2 = new User(…); System.runAs(u2) { }I guess this shouldn't count in any user limits, because in this way we are never creating a real user.2) I faced such problem in past. We handled such problem, by creating a custom setting that tells whether the code is running in test context. By default the custom setting is dead(no data), only test code puts some test criteria(data) to it. So the core apex code in triggers/controllers checks for that setting and loads limited data as per the criteria specified in Custom Settings.Good news is in winter'11 you will get a “New isRunningTest method for System. It returns true if the currently executing code was called by code contained in a method defined as testMethod. So you can use this new feature, to limit the data being loaded. So such custom setting hack will no more be required.Though, I know that its not a good way to handle this case, but.. Let me know if you find any good solution to this.

Leave a Reply

%d bloggers like this: