i want to catch the limit exception, but I do not how. This is my code:

queryCount = 'SELECT count(id) result FROM Product2 ';
queryCount = queryCount + getWhereClause();    
results = database.query(queryCount);

I have more than 70k Product2 records. How I can catch the exception to avoid put this error? I want to put more friendly error message

Too many query rows: 50001 Error is in expression '{!search}' in component in page pgselectproductwholeseller: Class.CnSelectProductWholeseller.search: line 112, column 1

  • 1
    try to use getLimitQueries() and getQueries() to determine how many rows you can query, and add LIMIT statement to your soql, and in case if amount of the records is same as limit -- put error message (or warning ). Trick is that you can not catch limit exception -- like CPU time, DML or SOQL amounts etc. – kurunve Jul 11 '16 at 8:14
  • 1
    @kurunve You know there are Limits.getLimitQueryRows() and Limits.getQueryRows() methods, right? Much more relevant here since OP is only consuming one query... – Adrian Larson Jul 11 '16 at 13:19
  • 1
    @AdrianLarson nice catch, I meant those methods, but failed with signatures. Thanks – kurunve Jul 11 '16 at 14:08
  • Did you ever make any progress on this question? – Adrian Larson Jul 29 '16 at 16:39

A couple things. Note right off the bat that the documentation on Exception Class and Built-In Exceptions states LimitException cannot be caught. You have to proactively prevent it.

If it fits your needs, you can use the @ReadOnly annotation to raise the limits of your page. Here is the relevant section of the documentation:

The @ReadOnly annotation allows you to perform unrestricted queries against the Force.com database...

Visualforce pages can call controller methods with the @ReadOnly annotation, and those methods will run with the same relaxed restrictions. To increase other Visualforce-specific limits, such as the size of a collection that can be used by an iteration component like <apex:pageBlockTable>, you can set the readonly attribute on the <apex:page> tag to true. For more information, see Working with Large Sets of Data in the Visualforce Developer's Guide.

If you don't need to perform any DML on your page, the above is by far the simplest option.

If you do need to perform DML, but still want to be able to know the number of Product2 records in your org on demand, I would consider writing a batch to save the data in a Hierarchy Custom Setting. It can be dead simple

public with sharing class Throwaway implements Database.Batchable<SObject>, Database.Stateful
    final SObjectType toCount;
    Integer count;
    public Throwaway(SObjectType toCount)
        this.toCount = toCount;
        this.count = 0;
    public Throwaway execute()
        Database.executeBatch(this, 50000);
        return this;
    public Database.QueryLocator start(Database.BatchableContext context)
        return Database.getQueryLocator('SELECT Id FROM ' + toCount);
    public void execute(Database.BatchableContext context, List<SObject> scope)
        this.count += scope.size();
    public void finish(Database.BatchableContext context)
        // Save to a Hierarchy Custom Setting - Org Default
        // You should probably accept an additional SObjectField parameter
        // If you wanted this solution to store this data for multiple objects

Then you can get the number of records as simply as:

Integer product2Count = ObjectCounts__c.getOrgDefaults().Product2__c;

It doesn't really come into play here, but you should know there is a method on the Database class called countQuery. If you only want to know the number of records a query returns, this method is preferable. It doesn't change your row limits (though it should consume marginally less heap and CPU time). But on a smaller table you could do:

Integer product2Count = Database.countQuery('SELECT count() FROM Product2');

As a last resort, you can find the maximum number of remaining rows using the Limits class.

Integer remainingRows = Limits.getLimitQueryRows() - Limits.getQueryRows();
query += ' LIMIT :remainingRows';
Integer queriedRows = Database.countQuery(query);

However, the above will always return remainingRows and exhaust what limit remains.

| improve this answer | |

There is no way by which you can catch a limit exception but a code of below type can help:

queryCount = 'SELECT count(id) result FROM Product2 ';
queryCount = queryCount + getWhereClause() +' limit 50000';    
results = database.query(queryCount);

/*This means that there were  50000 or more records in the database.*/
    apexpages.Message msg = new Apexpages.Message(ApexPages.Severity.Error,'Error:There were 50000 or more records to process');

The only problem with the above code would be it will work only up to 49999 records and will start failing thereafter.

| improve this answer | |
  • 1
    The problem here is that you can not use LIMIT in aggregate query – lopez.regalado.fj Jul 11 '16 at 17:26
  • @lopez.regalado.fj But it does not even need to be an aggregate query. Just use count() instead of count(id). – Adrian Larson Jul 13 '16 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.