Often times a customized Microsoft Dynamics CRM system will contain several custom reports written with SQL or fetchXML. When working with relational data, there are often multiple ways to query the data. If your reports take longer than 2 minutes to run, it may be time to review your queries for optimization.
The following are a few challenges we have been called on to assist with, including steps that we have taken to optimizing report performance.
1. Have proper indexes and a SQL maintenance plan been created?
2. How much data does your report return? If it’s significant, consider applying additional filtering within the report or split it into multiple reports.
3. Does the report contain Subreports? Subreports can often significantly add on to the reporting completion time. Consider if it’s feasible to instead add multiple data sets to your report or adjust the existing datasets to include joins.
4. Does the report have multiple datasets? Try running each from your SQL server to determine which one is causing the bottleneck. Then determine if there is a better way to query the data. Some common scenarios we have found:
a. Filtering is applied against large tables such as ActivityParty. ActivityParties are Users or other entities such as Contacts which are stored in Activity fields such as sender, recipient, etc. Often times this data will be the same as the Owner or Regarding of the Activity which can be retrieved directly from the activity entity. By adjusting your query to filter on one of these fields, you can often see a dramatic difference in querying time.
Filtering phone calls based on the owner would look similar to this:
Select * from FilteredPhoneCall where Ownerid = @User
Filtering phone calls based on the sender or recipient would look similar to this. In most orgs this can produce the same results as the above query, however, provide slower performance due to its complexity:
Select * from FilteredPhoneCall inner join FilteredActivityParty ON FilteredActivityParty.activityid = FilteredPhoneCall.activityid AND FilteredActivityParty.participationtypemask IN (1, 2) AND FilteredActivityParty.partyid = @User
b. Left outer joins. Generally, left outer joins will have slower performance than inner joins. However, they can also produce different results. Left outer joins will return all rows from the left table even if there is no match in the right table. Inner joins return all rows only if there is a match in both tables. Compare the results with a left outer join vs inner join in the query and determine if the difference is worth the performance impact.
With these simple changes, you can dramatically improve the performance of your reports while at the same time build user confidence in the efficiency of your CRM org! Don’t make your users waste any more time. Contact us today if you have questions or would like assistance in optimizing your reports.