{Tips & Tricks} How to optimize your report query in Dynamics CRM minimizing the use of filtered views

There are many topics on optimizing the performance of the reports in Dynamics CRM. And it has been discussed so many times about the performance of filtered views. However no matter how much we try, we have to use filtered views for the security context of the report.

And here I was called for to optimize the performance of another such report which uses filtered views. The report was constantly timing out. The report was basically a join of the FilteredOpportunity with around 10 master data entities and around 5 filter operators. And the total opportunities in the system were around 1,50,000.

So I had to optimize the report for the maximum possible records to be returned. I have to use filtered opportunity view but how do I minimize the performance effect of it. And while I was thinking, suddenly a trick came to my mind.

First I ran the query

Select * from FilteredOpportunity

The query completed in 2 minutes and 35 seconds. Then I ran the below query.


SELECT DISTINCT OpportunityId From FilteredOpportunity

ON O.OpportunityId = OP.ID

And voila!. The query returned the results in just 8 seconds. The reason is because the primary key column of a table is clustered index and the fetch on just the primary key would be very fast compared to the fetch of the other columns from the view.


I replaced FilteredOpportunity with the Opportunity entity view. And joined the Opportunity with the @OPPS table on the opportunityid column. Off course to retrieve the names text values of the option set and all, I had to join with the String Map table but after I constructed the query and ran the report, it consistently executed within 30 seconds even with the maximum set of data.


Hope this helps when you face the same problem while you develop SSRS reports in Dynamics CRM.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s