Context
I wanted to take a minute to discuss how to query record counts in Power Platform tables (entities). We have been doing this lately while working on a large migration with millions of records. When checking the count of an entity with a large number of records, the AggregateQueryRecordLimit is exceeded. Cannot perform this operation error occurs. The tests below prove the default limit for the AggregateQueryRecordLimit is 50,000.
OData Aggregate Query
The aggregate($count as count) will return the total number of records for the specified entity.
https://<Server>.api.crm6.dynamics.com/api/data/v9.2/accounts?$apply=aggregate($count as count)Limitations
An "AggregateQueryRecordLimit exceeded. Cannot perform this operation." response is returned when the entity has a large number of records. More information regarding the AggregateQueryRecordLimit can be found at https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/aggregate-data#limitations
{
"error": {
"code": "0x8004e023",
"message": "AggregateQueryRecordLimit exceeded. Cannot perform this operation."
}
}Can we Increase the AggregateQueryRecordLimit?
Old documentation and research suggest that the AggregateQueryRecordLimit default can be modified using the UpdateAdvancedSettingsRequest message in the Power Platform SDK for old ON PREMISE instances of Power Platform. These two articles provide a bit more detail.
You can request that the limit be increased through a Microsoft support ticket. I am pretty sure I have done this in the past to get an SQL Server Reporting Services (SSRS) report working.
Test 1 - Query record count of 49,999
A count 49,999 records can be counted. Request:
https://<Server>.api.crm6.dynamics.com/api/data/v9.2/contacts?$apply=aggregate($count as count)Response:
{
"@odata.context": "https://<server>.api.crm6.dynamics.com/api/data/v9.2/$metadata#contacts",
"value": [
{
"count": 49999
}
]
}Test 2 - Query record count of 50,000
A count 50,000 records can be counted.
Request:
https://<Server>.api.crm6.dynamics.com/api/data/v9.2/contacts?$apply=aggregate($count as count)Response:
{
"@odata.context": "https://<server>.api.crm6.dynamics.com/api/data/v9.2/$metadata#contacts",
"value": [
{
"count": 50000
}
]
}Test 3 - Query record count of 50,001
A count 50,001 records results in an error.
Request:
https://<Server>.api.crm6.dynamics.com/api/data/v9.2/contacts?$apply=aggregate($count as count)Response:
{
"error": {
"code": "0x8004e023",
"message": "AggregateQueryRecordLimit exceeded. Cannot perform this operation."
}
}
Comments