function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
tf_ianrtf_ianr 

Too many query rows exception

I'm getting the "Too Many Query Rows Exception" even though the combined queries in my code aren't returning more than about 2000 rows.

 

I ran a debug log. There are a two queries higher up that return about 700 rows each, then this one that returns 24 and triggers the error:

 

19:12:35.172|SOQL_EXECUTE_BEGIN|[165]|Aggregations:0|select id, LogType__c, UserLoginName__c, MachineName__c, TimeStamp__c, LogNotes__c from MyCustomTable__c where Event__c = :this.m_eventID and LogType__c in ('Download','Upload') and Attendance__c = null
19:12:35.196|SOQL_EXECUTE_END|[165]|Rows:24
19:12:35.196|EXCEPTION_THROWN|[165]|System.Exception: Too many query rows: 10001

This is confusing me. I understand that the limits apply to all queries exectued in the handling of a request, but does Salesforce count the rows that are actually returned for the queries, or is it counting the rows that the queries 'look at'?

 

In other words, how does this exception work exactly?

Best Answer chosen by Admin (Salesforce Developers) 
*werewolf**werewolf*

I suspect the issue is the query with the 2 group by's:

 

select UserLoginName__c, MachineName__c from TF_Events_Scanning_Log__c where Event__c = :this.m_eventID group by UserLoginName__c, MachineName__c

 

Could be that it's making some sort of cartesian product there and querying 752*752 rows, or maybe 752*9.  Why are you grouping by those things anyway if you're not using any aggregate functions on them?

All Answers

skodisanaskodisana

Hi,

 

Please verify your debug logs once again and check where more queries are returning.

Total number of query rows 10000 is per context. 

It counts the rows only that are actually returned for the queries.

 

Thanks,

Kodisana

*werewolf**werewolf*

Do you perhaps have a query further up that's doing a COUNT or some other aggregate function?  That can eat lots of query rows without seeming to return much.  Obviously it's going to error out on the last query it does though, that's the stick that breaks the camel's back, but it probably isn't the true culprit.

tf_ianrtf_ianr

Thanks for your suggestions. Perhaps you can figure it out from the debug log?

Here's my complete debug log for the request that causes the error:

 

 

 

16.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;VALIDATION,INFO;WORKFLOW,INFO
19:12:34.697|EXECUTION_STARTED
19:12:34.698|CODE_UNIT_STARTED|[EXTERNAL]|066200000001111|VF: /apex/EventScanningInfo
19:12:34.698|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController <init>
19:12:34.698|METHOD_ENTRY|[11]|MAP.get(ANY)
19:12:34.698|METHOD_ENTRY|[11]|System.PageReference.getParameters()
19:12:34.698|METHOD_ENTRY|[11]|ApexPages.currentPage()
19:12:34.698|METHOD_EXIT|[11]|ApexPages.currentPage()
19:12:34.698|METHOD_EXIT|[11]|System.PageReference.getParameters()
19:12:34.698|METHOD_EXIT|[11]|MAP.get(ANY)
19:12:34.698|SOQL_EXECUTE_BEGIN|[21]|Aggregations:0|select id,name,Start_Date_Time__c,Total_Places__c, Total_Bookings__c from TF_Event__C where id = :m_eventID
19:12:34.705|SOQL_EXECUTE_END|[21]|Rows:1
19:12:34.705|CODE_UNIT_FINISHED|EventScanningInfoController <init>
19:12:34.749|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController get(Event)
19:12:34.749|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController invoke(getEvent)
19:12:34.749|CODE_UNIT_FINISHED|EventScanningInfoController invoke(getEvent)
19:12:34.750|CODE_UNIT_FINISHED|EventScanningInfoController get(Event)
19:12:34.750|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController get(EventStartDate)
19:12:34.750|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController invoke(getEventStartDate)
19:12:34.750|METHOD_ENTRY|[32]|01p200000001111|EventScanningInfoController.LocalDateTime(Datetime)
19:12:34.751|METHOD_ENTRY|[204]|Datetime.format(String)
19:12:34.751|METHOD_EXIT|[204]|Datetime.format(String)
19:12:34.751|METHOD_EXIT|[32]|EventScanningInfoController.LocalDateTime(Datetime)
19:12:34.751|CODE_UNIT_FINISHED|EventScanningInfoController invoke(getEventStartDate)
19:12:34.751|CODE_UNIT_FINISHED|EventScanningInfoController get(EventStartDate)
19:12:34.754|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController get(AttendanceCount)
19:12:34.755|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController invoke(getAttendanceCount)
19:12:34.755|SOQL_EXECUTE_BEGIN|[37]|Aggregations:0|select id, Attendee__c, RSVP_Status__c, Attendance_Status__c from Attendance__c where Event__c = :m_eventID 
19:12:34.792|SOQL_EXECUTE_END|[37]|Rows:752
19:12:34.794|METHOD_ENTRY|[38]|LIST.size()
19:12:34.794|METHOD_EXIT|[38]|LIST.size()
19:12:34.795|CODE_UNIT_FINISHED|EventScanningInfoController invoke(getAttendanceCount)
19:12:34.795|CODE_UNIT_FINISHED|EventScanningInfoController get(AttendanceCount)
19:12:34.795|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController get(AttendanceStatusTable)
19:12:34.795|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController invoke(getAttendanceStatusTable)
19:12:34.795|SOQL_EXECUTE_BEGIN|[43]|Aggregations:0|select id, Attendee__c, RSVP_Status__c, Attendance_Status__c from Attendance__c where Event__c = :m_eventID 
19:12:34.824|SOQL_EXECUTE_END|[43]|Rows:752
19:12:34.826|METHOD_ENTRY|[47]|MAP.containsKey(ANY)
19:12:34.827|METHOD_EXIT|[47]|MAP.containsKey(ANY)
19:12:34.827|METHOD_ENTRY|[54]|MAP.put(ANY, ANY)
19:12:34.827|METHOD_EXIT|[54]|MAP.put(ANY, ANY)
... then lots of MAP operations ....
19:12:35.075|METHOD_ENTRY|[61]|MAP.get(ANY)
19:12:35.075|METHOD_EXIT|[61]|MAP.get(ANY)
19:12:35.075|CODE_UNIT_FINISHED|EventScanningInfoController invoke(getAttendanceStatusTable)
19:12:35.075|CODE_UNIT_FINISHED|EventScanningInfoController get(AttendanceStatusTable)
19:12:35.075|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController get(UploadDownloadTable)
19:12:35.075|CODE_UNIT_STARTED|[EXTERNAL]|01p200000001111|EventScanningInfoController invoke(getUploadDownloadTable)
19:12:35.076|METHOD_ENTRY|[74]|01p200000001111|EventScanningInfoController.getUsers()
19:12:35.076|SOQL_EXECUTE_BEGIN|[120]|Aggregations:0|select UserLoginName__c
			from TF_Events_Scanning_Log__c where Event__C = :this.m_eventID
			group by UserLoginName__c
19:12:35.119|SOQL_EXECUTE_END|[120]|Rows:9
19:12:35.119|METHOD_ENTRY|[126]|SObject.get(String)
19:12:35.119|METHOD_EXIT|[126]|SObject.get(String)
19:12:35.119|METHOD_ENTRY|[127]|LIST.add(ANY)
19:12:35.119|METHOD_EXIT|[127]|LIST.add(ANY)
... then several SObject and LIST operations ....
19:12:35.122|METHOD_ENTRY|[126]|SObject.get(String)
19:12:35.122|METHOD_EXIT|[126]|SObject.get(String)
19:12:35.122|METHOD_ENTRY|[127]|LIST.add(ANY)
19:12:35.122|METHOD_EXIT|[127]|LIST.add(ANY)
19:12:35.122|METHOD_EXIT|[74]|EventScanningInfoController.getUsers()
19:12:35.122|METHOD_ENTRY|[80]|01p200000001111|EventScanningInfoController.MachinesForUser(String)
19:12:35.122|METHOD_ENTRY|[184]|01p200000001111|EventScanningInfoController.EnsureUserMachineMap()
19:12:35.123|SOQL_EXECUTE_BEGIN|[138]|Aggregations:0|select UserLoginName__c, MachineName__c  
			 from TF_Events_Scanning_Log__c where Event__c = :this.m_eventID 
			 group by UserLoginName__c, MachineName__c
19:12:35.166|SOQL_EXECUTE_END|[138]|Rows:9
19:12:35.166|METHOD_ENTRY|[143]|SObject.get(String)
19:12:35.166|METHOD_EXIT|[143]|SObject.get(String)
19:12:35.166|METHOD_ENTRY|[144]|SObject.get(String)
19:12:35.166|METHOD_EXIT|[144]|SObject.get(String)
19:12:35.166|METHOD_ENTRY|[145]|MAP.containsKey(ANY)
19:12:35.166|METHOD_EXIT|[145]|MAP.containsKey(ANY)
19:12:35.166|METHOD_ENTRY|[151]|MAP.put(ANY, ANY)
19:12:35.166|METHOD_EXIT|[151]|MAP.put(ANY, ANY)
19:12:35.166|METHOD_ENTRY|[152]|SET.add(ANY)
19:12:35.166|METHOD_ENTRY|[152]|MAP.get(ANY)
19:12:35.166|METHOD_EXIT|[152]|MAP.get(ANY)
19:12:35.166|METHOD_EXIT|[152]|SET.add(ANY)
... then several MAP and SET operations
19:12:35.171|METHOD_EXIT|[152]|MAP.get(ANY)
19:12:35.171|METHOD_EXIT|[152]|SET.add(ANY)
19:12:35.171|METHOD_EXIT|[184]|EventScanningInfoController.EnsureUserMachineMap()
19:12:35.171|METHOD_ENTRY|[185]|MAP.get(ANY)
19:12:35.172|METHOD_EXIT|[185]|MAP.get(ANY)
19:12:35.172|METHOD_EXIT|[80]|EventScanningInfoController.MachinesForUser(String)
19:12:35.172|METHOD_ENTRY|[89]|01p200000001111|EventScanningInfoController.DownloadsForUser(String)
19:12:35.172|METHOD_ENTRY|[192]|01p200000001111|EventScanningInfoController.EnsureEventLogList()
19:12:35.172|SOQL_EXECUTE_BEGIN|[165]|Aggregations:0|select id, LogType__c, UserLoginName__c, MachineName__c, 
	TimeStamp__c, LogNotes__c 
	from TF_Events_Scanning_Log__c where Event__c = :this.m_eventID and LogType__c in ('Download','Upload') 
	and Attendance__c = null
19:12:35.196|SOQL_EXECUTE_END|[165]|Rows:24
19:12:35.196|EXCEPTION_THROWN|[165]|System.Exception: Too many query rows: 10001
19:12:35.196|METHOD_EXIT|[192]|EventScanningInfoController.EnsureEventLogList()
19:12:35.196|METHOD_EXIT|[89]|EventScanningInfoController.DownloadsForUser(String)
19:12:35.196|CODE_UNIT_FINISHED|EventScanningInfoController invoke(getUploadDownloadTable)
19:12:35.196|CODE_UNIT_FINISHED|EventScanningInfoController get(UploadDownloadTable)

 

There are six queries and they return return:

 

  • 1 row
  • 752 rows
  • 752 rows
  • 9 rows (but its a group-by query that looks at 752 rows)
  • 9 rows (but its a group-by query that looks at 752 rows)
  • 24 rows
So thats 3033 rows returned, even taking account of aggregation/group-by.
So why the Too many query rows error?

 

 

 

*werewolf**werewolf*

I suspect the issue is the query with the 2 group by's:

 

select UserLoginName__c, MachineName__c from TF_Events_Scanning_Log__c where Event__c = :this.m_eventID group by UserLoginName__c, MachineName__c

 

Could be that it's making some sort of cartesian product there and querying 752*752 rows, or maybe 752*9.  Why are you grouping by those things anyway if you're not using any aggregate functions on them?

This was selected as the best answer
tf_ianrtf_ianr

Yeah, you're right, it was the group by queries. They were hitting a lot more rows than I originally thought they were, but it only gives you the count of the final result so its hard to track down. Fixed now, thanks.

 

(edit: I'm using the group-by without aggregates as an alternative to 'select distinct' which afaik SOQL cannot do)