function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Vinnie BVinnie B 

Best way to get around the 50000 query row error message?

I have what I think is a pretty simple issue.  I'm developing a program that will run a number of queries nightly and populate a 'snapshot' object.  This object has fields such as Date and NumberOfContacts so that we can track growth over time.  However, when I do a query that returns over 50K records (i.e. SELECT count(Name) FROM CONTACT WHERE Active__c = TRUE) I get the 50001 query row message.

I can run this query easily in the Developer Console to get my results even if well over 50K.  I looked into dividing the query up into different segments (i.e. by CreatedDate) and using a Batchable class.  Both of these seemed like a fair amount of effort to simply run a query.  I figured I'd ask here to see if there's a better way to do this.

I do plan on putting this into a scheduleable job that will run at 3:00 am or so.  Running the code manually in production is causing the problem.  I'm not sure if the scheduleable aspect alone will make a difference.

Thanks!!

 
Norm Sherman SFNorm Sherman SF
If your code is inside Apex and intended to run on Schedule, you should be exploring Batch Apex, which allows you to run queries in the millions!
Vinnie BVinnie B
OK.  So I've figured out how to write a basic Class with Database.Batchable implemented.  This class does my basic query which gets the number of 'active' contacts in our system.  I'm assuming I can write that value to a field in my latest 'snapshot' object.  Unfortunately, I've got three or four queries that will pop the 50K barrier on their own.  Do I need to write a separate batchable class for each of these?  This seems extreme given that I can get each value with one simple query (the Count query).

Also, I have a number of other queries in this program and am finding that while my other queries are well under 50K each, I will often pop the limit collectively.  Right now I've got these all in one class.  If I call the queries from other classes will that let me get around the limit?

I appreciate the governor limits and understand the need for them.  It just seems in this instance I'm going to be doing a lot of work and coding but will end up using the same amount of resources or more.  :(
Norm Sherman SFNorm Sherman SF
Why do you need additional queries? Can you take advantage of the relationships provided by the Contact?
Vinnie BVinnie B
I don't see how using any relationships would help.  I simply want to programatically run a query that counts the number 'active' of contacts in our system.  It appears that if this number is over 50,000 I can't do that without using batch apex.

I am having some success with batch apex but still can't get the code in the execute portion of the class to run.  I'll keep at it.

I still would argue this is a heck of a lot of work to figure out to programmatically get the results of one single count query.
Norm Sherman SFNorm Sherman SF
I found that inside Apex, i.e through classes, triggers, etc your queries are limited to 50k rows. However, if you execute a count using the REST API, there does not seem to be a limit.

Try it yourself and maybe you could architect your application a little bit differently.

If you are going with the Batch class, you can implement a global count variable at the start method, and then have a loop inside the execute to interate the count variable for every "row" processed. That's the best you can do with a count above 50k inside apex. Your finish method then could save that value of count somewhere.
Norm Sherman SFNorm Sherman SF
But yes, I agree, it's definately "hackish" and it would be laughable to do outside of SFDC on a system that is not multi-tenant. But I guess that is the trade off, at least it forces you to be creative!