In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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:
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.
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:
select
SUM(po_amt),
po_useridname
from filteredpo_transaction,
FilteredAccount
where ( po_accountID = AccountID )
group
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:
select
SUM(po_amt),
fullname,
jobtitle
from filteredpo_transaction,
FilteredAccount,
filteredsystemuser
where ( po_accountID = AccountID )
and po_userid=systemuserid
group
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