Aggregate Query limit exceeded?

crm, dynamics-crm-2011, fetchxml, vb.net

Solution

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

Problem

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?

Original source