Aggregate Query limit exceeded?

Question

I've got a 2 part question:

First question: I know that there is a query limit of 5,000 records when querying records, for performance reasons, but is there a limit when doing an aggregate query? If so, what is it? I need only to query the number of records and for one entity I can get a result of 39,000+ records, but for another entity I see an error message like:

System.ServiceModel.FaultException 1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: AggregateQueryRecordLimit exceeded. Cannot perform this operation. (Fault Detail is equal to Microsoft.Xrm.Sdk.OrganizationServiceFault).

I've been looking all over online but can't find anything on a query limit for aggregate/count queries.

Second question: Is there a simple way to query the COUNT of a large amount of records without a performance hit? I wouldn't think doing a count should require too much overhead. If there IS a limit, my idea was to implement paging in a loop and just retrieve the count for a given page while incrementing the page number each time until 0 records was returned. However, when I tried this, it seems to ignore the page and count attributes of the initial <Fetch> tag.

I'm wondering if there is a better/easier way to do this?

Habitue

Answer 1

I can't speak much to your first issue, but it looks like someone on MSDN may have found a workaround. See especially the linked forum post for the practical solution.

For the second question, COUNT is relatively easy to handle. See the MSDN article on FetchXML aggregates for lots of examples.

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='name' alias='opportunity_count' aggregate='count'/> 
    </entity> 
</fetch>

The generated SQL for that query will be as follows:

SELECT
    COUNT(*) AS opportunity_count
FROM
    Opportunity

Gabriellegabrielli

Thank you. Like I said, I've got a pretty simple FetchXML query that is already returning the counts, I think it's just when the total count is over 50,000. I was hoping to not have to make any DB changes but it looks like that may be the only option. That, or a registry change. – Habitue

@lhan16: OK, I see what you were saying now. Outside of this and direct database queries, all I can say is "Good luck!" – Gabriellegabrielli

I think what I am going to do (as I've been told a DB change or Registry change is not an option at this point) is just catch the "query limit exceeded" error and display the total count as "50,000+". Thanks for the input and links!! – Habitue

updated link: social.msdn.microsoft.com/Forums/en-US/… – Entomb