In this post, I hope to touch on a few areas that can affect performance that maybe not everyone thinks of when planning for and analyzing the performance of their CRM system.
Grids and Queries
Advanced Find, System Views, and personal Saved Views all end up going through the same CRM query infrastructure. The CRM query model is quite powerful. Though you can’t do everything you can do in SQL, you can do quite a lot, which means it’s possible to craft very complex queries that affect the performance of the whole CRM system.
The ability to create system views should generally be restricted to admins and customizers, but for personal Saved Views, you may decide to restrict these as well, if you find that some users are creating queries that are too complex or are negatively impacting the system. Unfortunately, it’s not possible to retrieve saved views that you don’t own or that have not been shared to you in CRM. Creating a simple callout that will automatically share a created saved view with a particular team or a specific user who is a system admin is one option here. I would be interested in hearing if the inability for system admins to view these saved views has caused issues in any CRM implementation.
Hopefully everyone already reviews their System Views as part of their CRM implementation, but often queries that work well on small amounts of data may start performing poorly as your implementation ages and you have more and more data. It’s always a good idea to review your system views to make sure they are performing within your goals.
A couple of simple things to look for when you are tuning a System View (or a Saved View):
- Are you retrieving too many attributes? Retrieving large numbers of attributes, especially those that are not covered by any index, may cause SQL to choose a plan that results in a slow, expensive query. Try to retrieve the minimum number of attributes you need to satisfy the view.
- Are your sorting choices resulting in a slow, expensive query? Choosing the right sort criteria can make a huge difference in your query. If the sort attributes cannot be determined from an index key, it may result in a slow, expensive query. Make sure the default sort order is performant. Another thing to examine is sorting on custom attributes and logical attributes. Due to how SQL handles sorting on a column for a joined table, these kinds of queries can result in queries that don’t perform as well as you’d like.
- Are you linking to many other entities in your view definition? The more linked entities you have, the more complex the query and this can impact performance.
CRM has several automated mechanisms that help keep the system performing well, but for best results, it’s always good to examine these things directly with the knowledge of your customizations and usage patterns.
Offline Data
Another kind of saved query data that can affect the performance of the system are the definitions of the data that is brought down to the Laptop client. This is configured via “Local Data Groups” in the Laptop client.
As with Saved Views, you may want to examine how users are customizing these views and give guidance on how to customize for best performance.
A couple of simple things to look for when you are tuning a System View (or a Saved View):
- Is the selection criteria too broad? CRM ships with default criteria set to some combination of objects the user owns and/or items that have been modified recently. If users customize these to select a lot more data, it will be expensive to compute on the server what the user should bring offline, and also expensive to transfer the larger amount of data to the client.
- Too many data groups. The more data groups defined for each entity, the more complex the computation must be on the server to determine what should be taken offline for the user. Sometimes multiple groups can be combined into a single group or eliminated altogether because they are redundant.
Wrapping Up
Hopefully at least one of the areas mentioned above has some tuning opportunities you haven’t yet explored for your CRM implementation. It’s important to understand how your users are taking advantage of the rich customization tools CRM provides to get an accurate picture of the performance of your implementation.
If you have other performance topics you might want discussed in a post, please mention it in a comment – we’d love to hear about it.