crminthefield: Improving Dynamics CRM Report Performance – Part 1 “The Basics”

[Blog bot]

Источник: http://blogs.msdn.com/b/crminthefiel...rformance.aspx
==============

A lot of times customers have questions around how to improve reporting performance in Dynamics CRM. Of course, with every CRM deployment and report the answer may be different, but some tips can be applied more generally. In this post I will discuss some basic tips and tricks to improve your Dynamics CRM reports, specifically around query tuning and using Visual Studio’s Business Intelligence Development Studio. This is the second blog post in the CRM Reporting Series, you can find the first post in the following link which focuses on the different options for querying data and building reports out of CRM: Custom Reporting in Microsoft Dynamics CRM - Fetch vs. Filtered Views

When thinking about reporting, the total time that it requires to generate a report can be split into three different actions:

1. Time it takes to execute the reports queries on the SQL Server.
2. Time it takes to process the report on the reporting server, based on the result set of the executed queries.
3. Time it takes for the report to render.

There are many possible culprits of reporting performance issues and often users jump to the conclusion that it has to be a server side issue. Maybe so, though many times we see that report performance issues in CRM are directly related to the datasets, queries and report formatting logic within the report that could be optimized for better performance. The following are suggestions for optimization as you begin to troubleshoot:

Suggestions for all Dynamics CRM Deployment Types:

The following summarizes the recommendations that can be performed using reporting tools for both CRM On-Prem and CRM Online, specifically what can be done using either SQL Queries OR FETCHXML, which is the only option for CRM Online.

Optimize Report Queries & Retrieve the Minimum Amount of Data Needed in your Report

• Review the number of fields returned in each dataset, you will likely find that they contain more columns than used in the report. The more columns returned the slower your query execution, if there are fields that are not being using in the report, they should be removed from your query.
• Ensure all queries being executed are being used in the report. When a report is run every dataset in the report will be executed, regardless of if it is being used in a report. A lot of times new datasets are added during building of reports and not cleaned up later on. Check to ensure that all datasets are still being used, for instance, datasets for available parameter values and if it is determined that there are data sets no longer being used be sure to delete them from your report.
• If possible, add filters and parameters to reduce the number of records returned, large result sets (more than 250 records) are more like a data export. By adding parameters that users must specify when they run the report, such as date ranges or owner, or by adding additional selection criteria to a report you can greatly reduce the data that is filtered for the report each time it is run by a user.
• Look very critically at the need for the large result sets and scale back if possible. The time it takes to render a report will increase based on the number of records being returned, as well as the number of data sets in your report. If the details are only used in a small amount of the situations, create another report to display the details which will avoid the retrieval of all details in the larger number of situations.
• Using ORDER BY in the dataset differs from the ORDER BY in the Tablix\list. You need to decide where the data will be sorted. It can be done within SQL Server with an ORDER BY clause in the dataset or in by the Reporting server engine. It is not useful to do it on both sites. If an index is available use the ORDER BY in your dataset query.
• Additionally, a lot of times data is GROUPED in the report without any drill down, in that scenario perform the GROUP BY in your dataset query which will save on data transfer to the SQL Server and it will save the reporting server engine having to complete the GROUP BY.

Reporting Services (Business Intelligence Studio) Recommendations

• Keep in mind where you are using page breaks, reports are rendered into pages based on where page breaks occur. Although the entire report is processed before the rendering phase, the performance of the rendering extension is significantly impacted by the page size.
• Using a primary report to operating the parameters and filtering aspects and then passing those parameters to a sub report. This can have a beneficial benefit because it can avoid issues with how parameters and data sets operate.
• Try restricting your report from first parameter. Sometimes when you select first parameter, based on that you need to fill in a second parameter option set. If there are hundreds of records then also reports will be very slow. In that case remove those parameters, if possible.
• Limit Text Length and Number of Items in Charts, reports use only some of the possible chart types from Reporting Services. For any chart type, limiting label length and number of items is recommended for the chart contents to be displayed correctly. When Reporting Services displays a chart with long labels, the chart itself becomes too small to be usable.
  • Limit your chart label length, truncating it if it is necessary.
  • Consider limiting the number of items displayed in charts.

CRM On-Prem Only Suggestions

• Use the SQL Profiler to measure the performance of all datasets (Reads, CPU and Duration), to determine where your more expensive queries may be happening and identify if they can be tuned.
• Ensure that you are NOT using the dreaded “SELECT * FROM Table/View” syntax that returns all fields for a table or view. For those of us who are report developers, at first this seems to be the easy way, though retrieving all columns creates a lot of overhead, especially if multiple data sets are using this syntax. Assume you use 7 columns in the tablix\list, change the syntax of the dataset to only return these 7 columns.
• When comparing dates, use the UTC date fields for comparisons. For example, compare the createdonutc fields and not the createdon fields in a filtered view.
• Using SQL JOINS
  • One of the best ways to boost JOIN performance is to limit how many rows need to be JOINED. This is especially beneficial for the outer table in a JOIN. Only return absolutely only those rows needed to be JOINED, and no more.
  • If you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have their own indexes.
  • If you have two or more tables that are frequently joined together, then the columns used for the joins on all tables should have an appropriate index.
  • Avoid joining tables based on columns with few unique values. If columns used for joining aren’t mostly unique, then the SQL Server optimizer may not be able to use an existing index in order to speed up the join. Ideally, for best performance, joins should be done on columns that have unique indexes.

Additional Considerations to take when optimizing your reports:

• Do not make a report with a large dataset or a complex SQL query available on-demand to all users. Instead schedule a snapshot in Report Manager during a time schedule when the system is lightly loaded.
• How the software and reports are being used and if the process can potentially be improved. (IE: Can you replace reports with views and/or dashboards?)
• If Microsoft CRM for Outlook is used and if users go offline which can tax system resources.
• Peak usage patterns, understanding which reports are being used when and if those reports can be tuned for optimized performance.
• You could also opt to create a data warehouse(another database). The drawback is you will have to refresh this data warehouse with your CRM data every few hours.

For additional perspective on the different options that the Microsoft Dynamics CRM 2011 SDK provides for retrieving data programmatically, check out a recent post by Austin Jones explaining Dynamics CRM 2011 SDK Query Limitations by API.

If you are interested, our PFE team is ready to help you with this, we can assist with query examples and various other engagements to help you improve your CRM reports and performance. In addition, we have many other services we offer such as reporting workshops, developer training, admin workshops, and code reviews. If you would like to have another Microsoft PFE or I visit your company and assist with the ideas presented on our blog, contact your Microsoft Premier Technical Account Manager (TAM) for booking information. For more information about becoming a Microsoft Premier Customer email PremSale@microsoft.com.

Thanks!

Sarah Champ

Microsoft Premier Field Engineer







Источник: http://blogs.msdn.com/b/crminthefiel...rformance.aspx