Recently I had a privilege to work on a xRM project utilizing CRM 4.0 and SQL Reporting Services to create very complex Business Intelligence reports base on transactional records which were housed inside MS CRM.


We had approximately:

  • 25,000 accounts, which were assigned to one or more systemusers through sharing
  • 3,000,000 detail transactional records, linked to the account
  • 400 systemusers

Simplified, the relationships looked like following:

Basic requirement of reporting was to slice and dice sum of transactional data based on users or some account level information.

Some Important findings:

Performance of FilteredViews degenerates fast with more complex queries involving multiple entities. For example, query giving user-grouped sum ran in 20 seconds. Below a simplified core of the query:



from     filteredpo_transaction,


where ( po_accountID = AccountID )

by po_useridname

A requirement for adding user title came up, we can do this by joining systemuser table to the query. This does not seem like a large change, but performance fell to 250 seconds. Below is the simplified core of that query:




    from filteredpo_transaction,



    where ( po_accountID = AccountID )

    and     po_userid=systemuserid

by FullName, jobtitle

Doing the similar comparison with non-filtered views, we experience drop from 15 seconds to 120 seconds, which helps but is not enough for user-friendly performance.

Account-Transaction Account-Transaction-User
Filtered 20s 250s 1250%
Non-Filtered 15s 120s 850%

Note that when using Non-Filtered Views, you will need to account for security yourself in the report criteria/filtering.

Custom Indexes

While MSCRM has indexes of its own, when you create custom relationships, these relationships are not indexed by default. In my example case, if I create custom indexes for transaction data and also for the system user assignment, the numbers will change drastically:

Account-Transaction Account-Transaction-User
Filtered 20s 15s -25%
Non-Filtered 5s 2s -60%

The interesting details are that by introducing extra table to query actually improves query performance in this example. This is due to better utilization of indexes. Similarly the Account-Transaction filtered query did not benefit from the index at all.

Note that adding Indexes may slow down the creation and updating of the records.

Final Words

This example shows quite a dramatic improvement from unusable 250s report to blazing fast 2s report. While the results will vary, it shows that you do not have to accept slow performance as direct result of large number of rows or complex data model. With correct planning and design, the speed can be improved.

If this is something you are looking for help on let the CRM Experts at PowerObjects know – we have many tools to help you get the reporting you need out of your MSCRM system.

Happy CRMing

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.