Using Aggregate SOQL With Large Amounts of Data (> 500k records)

Question

I am facing a issue about aggregate soql. My basic data object more than 500,000 record. But, i want to aggregate this object data. When i execute following data, SFDC will report the error for me. About, need to use querymore() function. And, found that, only 200 record return to me. In fact, I just want to get aggregate result in apex list. So, how to working on large aggregate soql?

My Error SOQL:    
SELECT SUM(CALL_Total_abc__c),Customer_abc__c FROM HOSP_SPECIALTY_PROD_KPI_abc__c WHERE BI_UPDATE_DATE_MSD__c = 2014-09-01 group by Customer_abc__c 

Answer 1

I note that your query includes:

WHERE BI_UPDATE_DATE_MSD__c = 2014-09-01

which may be selective enough to allow your query to work. As Working with Very Large SOQL Queries describes, that term would have to result in less than 10% of the rows being referenced.

If your data has that pattern i.e. the rows are distributed across many BI_UPDATE_DATE_MSD__c values, then adding an index to that field should allow the query to complete. (Adding an index allows the database to jump to just reference the specific rows rather than having to scan all the rows for matching values.)

You can get the field indexed via a support request. (The other technique that results in an index of marking the field to be an External ID isn't available for Date fields.)

Answer 2

This may be a good case for using a Stateful Batch process to aggregate your totals. You can iterate over all 500,000 records and keep track of whatever totals you need to, and then at the end do your DML in the finish() method. This can then be scheduled to run regularly through a Schedulable class.

Answer 3

The only way to use Querymore() will be to make the page readOnly .

Your visualforce page will have a ReadOnly attribute and set this to true ,this will relax the limit .

readOnly boolean if set enables read-only mode for a Visualforce page. In read-only mode, a page may not execute any DML operations, but the limit on the number of records retrieved is relaxed from 50,000 to 1 million rows. It also increases the number of items in a collection that can be handled by iteration components, from 1,000 to 10,000. If not specified, this value defaults to false.

Also to increase the performance of the query would recommend you to order the query

SELECT SUM(CALL_Total_abc__c),Customer_abc__c FROM HOSP_SPECIALTY_PROD_KPI_abc__c WHERE BI_UPDATE_DATE_MSD__c = 2014-09-01 group by Customer_abc__c order by SUM(CALL_Total_abc__c)