{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.

DECLARE @OPPS TABLE
(
    ID UNIQUEIDENTIFIER
)

INSERT INTO @OPPS
SELECT DISTINCT OpportunityId From FilteredOpportunity

SELECT *
FROM Opportunity O INNER JOIN @OPPS OP
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.

Advertisements

Author: Debajit

I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s