Dynamics CRM Quick Find Performance, Part II
You will have seen our recent blog of Dynamics CRM 2011 Quick Find Performance & Records Per Page Setting
This is part II for additional tuning steps for Quick Find.
In CRM 2013, the Index Management job automatically adds indexes for Quick Find columns, up to 20 in total. This does not happen automatically in CRM 2011, but the indexes can be added manually (as long as you are on UR10 or later), to get the same performance benefit.
Some of the keys you’ll want to call out here include:
· Indexes should only be applied for “Find Columns” and NOT “View Columns” for the Quick Find.
· The indexes will NOT be used if the you enter a wildcard (*) at the beginning of the search.
· Make sure you check the indexes already on the table before applying a new index, to ensure you are not applying duplicates.
The below content is a summary on how to create the indexes:
Indexing standard fields
While some of the standard fields will be included in custom indexes, many are not. Using the Account entity as an example, AccountName is included in indexes already, however the Fax field is not. Thus, if you add the Fax field as a Find Column for the quick find, we will need to add an index for this field. This can be done using the index below:
CREATE NONCLUSTERED INDEX IndexName
ON [dbo].[AccountBase] ([Fax])
GO
Indexing custom fields
Since custom fields are not indexed by default, if you add a custom field as a Find Column, you will certainly notice performance degradation if you do not index this field. The difference between custom fields and standard fields is that a custom field resides on the ExtensionBase table as opposed to the Base table. To index a custom Account field named customfield, you can use the index below:
CREATE NONCLUSTERED INDEX IndexName
ON [dbo].[AccountExtensionBase] ([new_customfield])
GO
Indexing address fields
A third type of field that could be added to a quick find is an address field. These fields are also stored in a different table, thus adding them without adding a corresponding index will also have an effect on performance. If you add Address1_Line1 to a quick find, the following index should be created:
CREATE NONCLUSTERED INDEX IndexName
ON [dbo].[CustomerAddressBase]
(
[Line1],
[AddressNumber],
[ObjectTypeCode]
)
INCLUDE ([ParentId])
Best Regards
Dynamics CRM Support Team
Share this Blog Article on Twitter
Follow Us on Twitter