12-11-2019 02:27 AM
We have an application which needs to retrieve the activities for a certain opportunity..
I've been told by Simon Burton to do that this way:
/api/activities?$filter=opportunities/any(o: o/id eq 23098978-efcf-41a3-b03f-22b2b1addb14)
Which works perfectly on my test machine with only a handful of activities.
On our live database server however, which has literally several hundreds of thousands of activities, it just crashes after half a minute with an OutOfMemory exception.
I'm strongly under the impression this feature is implemented in a way that it first retrieves ALL activities, and then afterwards iterates their opportunities with LINQ and pushes the matching result out...
Yesterday evening we've had a meeting in which I asked that this is going to be the correct way, and stays that way. That was answered with a yes.
So.. what to do about this?
12-12-2019 05:03 AM
The API currently does not support getting activities by an opportunity at this time, so doing an OData in-memory query as you did, would be the only way to achieve this. However, when pulling so many records back it will consume a lot of system resources and I’m not surprised that you are getting OutOfMemory exceptions with that many records. Try paginating the results to limiting the size of the payload. You could also limit the payload size by selecting certain fields. This goes for all API calls, you should always be paginating your API request, especially if you expect a lot of rows to be returned.
Try bring a smaller payload size back with $top|$skip
/api/activities?$filter=opportunities/any(o: o/id eq 23098978-efcf-41a3-b03f-22b2b1addb14)&$top=20&$skip=0
You could also reducing the size even further by using $select.
/api/activities?$filter=opportunities/any(o: o/id eq 23098978-efcf-41a3-b03f-22b2b1addb14)&$select=id,subject,…&$top=20&$skip=0