Enhance/Improve the Performance of MS CRM/ SSRS Reports
Common for both Fetch- based and SQL – based reports
- Limit a report to display information from a specified time period, instead of displaying all records in the Microsoft Dynamics 365 database.
- Calculate aggregate totals by using aggregations in a FetchXML query or a SQL statement, instead of passing raw data to Reporting Services and grouping.
- Pre-filter a report so that the dataset is limited.
- Limit the number of datasets used, if possible.
- When you compare dates, use the UTC date fields for comparisons. For example, compare the createdonutc fields and not the createdon fields in the FetchXML query or a filtered view.
SQL – based Reports Only
- Don’t create a report that uses a large dataset or a complex SQL query available on-demand to all users.
- Don’t select all columns from a Microsoft Dynamics 365 filtered view. Instead, explicitly specify the columns that you want to select in the SQL statement.
- Use SQL stored procedures instead of inline SQL.
