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

CRM Live Purge

So you've successfully conducted your Dynamics Sure Step Proof of Concept and your customer has decided to pull the trigger and implement CRM. Congratulations. You now quickly transition into implementation mode and the first step you need to do is purge your pilot data from your CRM environment. Or... you've successfully migrated your CRM data but you discover through testing that some data was deficient and you need to purge and reimport. Whatever the scenario your requirement right now is that you need to purge your data.

What is the best way of going about this in a CRM Live environment?

Well, technically this post does not only cover CRM Online - it can address purging in any CRM environment. However the reason why I'm addressing the CRM Live environment is because you can of course only purge data via the front end, 3rd party tool (e.g. Scribe) or using some programmatic solution that leverages the CRM APIs.

... And of course you should be doing the same in any environment, but let's just say that pragmatism sometimes sets in. For example, the size of the data makes the purge exercise a very slow one. So while I'm not going to go into it there of course "unsupported" ways of purging a CRM database in an on premise environment that are a thousand times more efficient than going via the API.

Anyway, back to the issue at hand - how do we go about purging? There are a number of ways:

1. Programmatic - Create a small .Net application. This should be pretty straight forward but requires a coded solution and you could run into problems with timeouts for large data sets.
2. 3rd Party e.g. Scribe - Build DTS's to purge the data. Again quite straight forward but assumes you have access to such a tool
3. Front end - you can of course use CRM's front end. However going from entity view to entity view and deleting that way would be highly inefficient both because you are limited to 250 rows per deletion and because you'd need to remember to repeat the same steps each time - assuming you need to purge more than once. So a more efficient way is to use the Bulk Record Deletion Tool. The remainder of this post will discuss using the Bulk Record Deletion as a purge tool.

The benefit of using the Bulk Record Deletion tool is that it essentially documents the steps required for the purge and it can subsequently easily be re-used to perform subsequent data purges. Below are the steps required to facilitate this:

Use the Bulk Deletion Wizard to delete all records from the entity in question (or you can limit this to a subset of records you wish to purge by filtering appropriately). Repeat for each entity you wish to purge.

Name the job appropriately and set a recurrence for 365 days. If you do not set a recurrence you will not be able to reuse the job at a later point in time so this is set only to facilitate reuse.

Now all your purge jobs will appear under the "Recurring Bulk Deletion System Jobs" and when you want to re-run the purge you can highlight all your purge jobs and select More Actions | Modify Recurrence to re-run the jobs immediately.

NB: It goes without saying that you need to create a very conspicuous reminder to cancel your purge jobs once you have cutover to production or else you might be in for a nasty surprise down the line... Use responsibly and at your own discretion.

System attribute cannot be delete because it used in one or more workflows

This follows my previous blog entry on Removing Artifacts.


MSCRM does a pretty good job so that you don't get into trouble when removing attributes from the system. Before you will be allowed to do so, it will verify that the attribute is not being used in any published form, view, or workflow in the system. For forms and views, the error message explains quite clearly which form/view needs to be updated before the attribute can be removed.


However when it comes to workflows, you just get the following generic error:

If you have hundreds of workflows it can be quite a daunting task to locate the offending one. So how can the workflows be identified? Query the database. You can use the following query to get you going:

select distinct name fromworkflow

where rules like '%custom_attributename%'
and deletionstatecode = 0
and statecode = 1
and primaryentity = 112

One thing to be cautious of is that there may also be active workflow instances that reference the field that you are removing and CRM does not (in my experience) prevent the attribute from being removed on account of an active worklow instance. Only published workflow definitions that reference a particular attribute will be prevent that attribute from being deleted. This means that you should take into consideration the impact that deleting an attribute will have on active workflows that might be running. If you do so and there is an active workflow instance, you can expect to see something like the following appear in your workflow:

A similar "invalid condition" will be shown in any unpublished/draft workflow definitions that reference the deleted field.

To help prevent such a scenario from occurring, you can also query the database to discover workflow instances of the published workflow definition that you identified in the above query:

select statuscode from AsyncOperation

where Name like 'name of workflow'

and statuscode in (0,10,20,21)

As perhaps a better alternative, you can also use the CRM Advanced Find to query these records and take the appropriate action:

Removing Artifacts

Over time as you go about using and configuring your CRM solution it is likely that you will replace an older design with a newer design to facilitate some business process. This may come about for a variety of reasons:

·        New features in a newer version CRM that allow you to go about solving the problem in a more efficient, direct way

·        Budget constraints that restricted the original design – perhaps there is now budget to purchase a 3rd party solution to replace an interim solution that was put in place

·        Lessons learned – both in terms of technology, experience gained from other implementations, or feedback from end users who decide that the solution needs tweaking

Whatever the reason, it's important to keep the solution clean. By this I mean removing the "artifacts" that may build up as part of this natural evolution of configuring CRM within an organization. Leaving in attributes that are no longer in use is not good practice for a number of reasons, some of which are:

·        Over time it can lead to confusion when trying to understand how a particular part of the application works. Keeping the system nice and clean is good in and of itself but also saves time in the long run by not having to spend time trying to separate between the wheat and the chaff

·        Although you may have cutover to the newer functionality it's possible that there are old reports or the like that still reference the old artifacts. If you remove these attributes the reports will fail the next time they run, however it is better to have a report fail noisily (of course expectations should be set) so it can be addressed, than continue to function and return wrong and/or misleading information

·        Performance – This one is fairly obvious. To keep the system running at optimum levels it is always advisable to keep them as lean and mean as possible.

       

Of course, removing artifacts doesn't have to be performed as part of the cutover to the new functionality. In many circumstances it may be prudent to wait a day or two, or even a week or two after a cutover event. For example, it may be possible that data was converted as part of the cutover event, but perhaps certain data got missed or skipped over (of course, this never happens when working with Sentri!) – having the artifact data available can allow you to correct the data quickly. Alternatively, it might be possible that for whatever reason you need to rollback to the previous functionality and of course having the old attributes along with the accompanying old data makes this that much easier to do. In short, it is recommended to create a follow up task pending a cutover event to remove artifacts from the system at a not too distant time in the future.