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.