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.
Best Answer
Apex uses more resources than a simple API call, such as extra memory, CPU time, etc, so there's additional limits in place. The Developer Console, contrariwise, uses the API, which is just a server-side database cursor. As such, you can technically query up to 50,000,000 rows in the Developer Console (though very large queries may time out or freeze your browser).