Hi,
Recently we were analyzing the performance of a fetch xml based online report.
The report was running against opportunity, and had 4 data set in it. Custom entity A and custom entity B were n – 1 related to opportunity.
| Data Set Opportunity | 13000 records |
| Data Set for Custom Entity A used for Lookup | 100 records |
| Data Set for Custom Entity B used for Lookup | 8000 |
| Data Set for Tasks Activity used for Lookup | 130000 records |
More on Lookup Function in SSRS
| Running without filter | 107 seconds |
| Filter Last 6 months | 94 seconds |
We then updated the report’s design, to include only one Dataset by using link entities in the main dataset’s fetch xml. We got the below result
| Running without filter | 280 seconds |
| Filter Last 6 months | 10 seconds |
Another variation we tried was having two Dataset, one for opportunity and another one for tasks to be used for lookup.
| Running without filter | 50 seconds |
| Filter Last 6 months | 50 seconds |
The last variation we tried was using only one Dataset for Opportunity in the main report and the drill down report for Tasks
| Running without filter | 34 seconds |
| Filter Last 6 months | 7 seconds |
It clearly shows that the most efficient way of implementing is through the usage of drill down report, only glitch here is user needs to click on the main report to get the details.
Using single dataset is efficient for subset of records, however it can take long time if it runs on huge number of records in which case lookup performs better.
Basically,
Hope it helps..
One thought on “SSRS Fetch XML based CRM Online Report performance in Dynamics 365”