We are querying an entity with a odata string like:
https://myinstance.crm99.dynamics.com/api/data/v9.0/myentity?$expand=my_field1_ref($select=field1,field2),my_field2_ref,my_field3_ref($select=field1, field2, field3),...&$orderby=createdon+desc&$filter=_my_entity2_ref_value+eq+696f0125-d1cf-e813-a95f-000d3ab490f3+and+statecode+ne+1
This query has altogether 7 joins and returns about 1000 rows.
The execution takes 16 seconds and 190 seconds where used for the download in chrome. It is using all fields from "myentity".
When adding a $select part before the expand, only the listed fields are returned and the execution time reduces to 11 seconds and the download in chrome takes only 48 seconds.
At last I changed the query to return only the ids instead of the joins. Then the execution time including the download reduces to 170 ms.
Can anyone explain why the performance for the join is so poor? As adding a join is a standard in SQL server, it seems obvious to use joining because it should be fast.
Some online searches yields results from SAP support, ASP.NET core, etc which talks about the same OData expand performance limitations. That means this issue is not limited to Dynamics odata web api only. The query with expand for joining entities will compose more child queries unlike SQL joins & hence the performance degradation.
I would recommend you to build FetchXML queries for same criteria you need & can query with the web api endpoint. This should be efficient due to native platform support. Read more
https://[Organization URI]/api/data/v9.0/contacts?fetchXml=<encoded query>
You can compose the Advanced find query in CRM & download the fetchXML query. Or write it in XrmToolBox fetchxml builder.