Filtered View Performance

As is well known it is best practice to use Filtered Views when performing queries on the CRM database most notably for report writing. This is because doing so adheres to the SDK standard and also yields many benefits such as:

• Security model adherence - reports written this way will automatically adhere to the CRM security model without having to do anything further.
• Exposing "name" attributes for Options Sets (including "Two Options") - these are denormalized in the filtered view so writing additional joins to retrieve the friendly names is unnecessary
• Embedding and Context Awareness - reports can be easily embedded into the CRM forms and run based on the current context without further input
• Advanced Find pre-filter - This filter mechanism becomes available eliminating the need to hard-code parameters in the report definition
• Formatting - Date, number, and currency formats defined in CRM can easily be applied to CRM reports for a consistent experience
• Built in parameters - Various other built in parameters that the CRM platform can leverage to simplify the report writing experience, avoid unnecessary hard-coding, and ensure consistency and cohesiveness between the CRM application and custom reporting (e.g. CRM_URL)

There is one downside - there can be a performance degradation when running queries against Filtered Views (vs. say the regular entity views). Of course this can be very significant as if your report takes a long time to run it more than negates all the benefits cited above.

However, the query performance of Filtered Views needs some additional analysis. From what I've read, most attribute this degradation to the fact that the Filtered Views (unlike other views) perform additional security checks against the CRM security model. And while this is indeed the case, my experience has indicated that this particular aspect does not really impact adversely. In fact, this additional check is fairly trivial (it might be that in cases where there is a heavily nested security hierarchy that this impacts more - so not to be completely disregarded).

So what causes the performance degradation? The following analysis of queries against various entities (customized - not OOTB) is very telling:

The crux of the analysis are columns 2 and 3. For example, "account" has 118 Option Sets and 169 Two Options for a total of 287 Options. As you can see, a simple query against the FilteredAccount vs. account entity yields a difference of 20 seconds! That drops dramatically for "event" where there are only 191 Options - dramatically but still not sufficient as the query difference remains at 6 seconds. The difference drops as the number of Total Options decreases such that when there are around 100 Options the difference becomes fairly trivial.

So in short, it is the number of Options on the entity that are the major cause of query degradation and that can be attributed to the additional joins required to obtain the friendly name (as described above). Consider that in the case of account the Filtered View is performing a little over 287 additional joins as opposed to the regular entity view ("a little over" as there are a few other joins required for the security model interrogation). And also the analysis shows that the degradation is exponential rather than linear after the "degradation threshold" is hit.

So what are the takeaways?

First of all it's important to mention that this performance analysis was run using SQL 2010. The experience might be otherwise on later versions. But according to the literature the number of query joins in this version of SQL is only limited by system resources (as opposed to previous versions where there was a hard upper limit). So one way of minimizing this issue is to add some horse power to the SQL Server. 

Secondly, it is noteworthy that the row count has no noticeable impact on query difference (for example, see row count of "account" vs. "invoice"). And similarly the difference is not caused by indexing issues. The basic Filtered View query leverages existing system generated indexes.

Third, the number of Option Sets shown in the analysis has to be slightly unusual. In most cases, I would imagine that there would be far fewer of these and therefore using the Filtered View for your queries will simply not have any noticeable impact on performance (although it's easy to run a quick comparison query to determine if this statement is correct or not).

Fourth (and perhaps most importantly), make sure to purge unnecessary or legacy attributes. The likelihood in cases where there is such a high number of options is that many of them have fallen into disuse over time. Opening the entity form and viewing how many of them no longer even appear on the form will probably shed some light here. This is not only good practice from a query performance point of view, but also helps to keep the data neat and clean which in turn contributes to a simplified and confusion-free user experience.

Finally, information is power and understanding what is underlying a Filtered View can be factored into design decisions well in advance. In fact, it is very likely that the reasons for going with a whole bunch of custom Option Sets vs. product-line-item kind of design have fundamentally changed with recent overhauls of the CRM user interface. In previous versions, the number of clicks to add line items was often prohibitive - an experience which has been significantly optimized especially in CRM 2013 (and probably more so with the very imminent CRM 2015 release).