Problem Statement :
Lookup is unresponsive and painfully slow in Dynamic CRM.
To identify the cause which is making Location lookup to be unresponsive.
Lookup in Dynamic CRM
A lookup represent parent child relationship between two entities.
For an example :
An Account (Organization) can have many contacts associated.
CRM by default have 1-Many relationship between account and a contact.
What SQL Index has to do with Lookup
Purpose of Indexes is to fine tune search operation in a table/entity.
In Dynamic CRM each table/entity have a Primary key which becomes a unique clustered index.
One table can have only one Unique-Clustered Index and can have Many Non-Unique-clustered indexes.
If overall Dynamic CRM is suffering from a performance issues, one may think of Re-building Non-Unique Clustered Indexes.
- It is recommended to schedule a job which takes care of Re-building indexes from time to time.
- Re-building of indexes may involve extremely high usage of memory, CPU and other resources so it should be done mostly during non-working hours, to avoid impacting business operation.
One should be able to analyse and see if overall CRM performance is at stake or there are few Lookup’s which may causing a performance problem and need to be taken care off.
Now, let see how internally Lookup types get represented in SQL.
Diagram is from On-Premise solution but it would be same for On-Line CRM.
A Primary Key is a 32 Bit Guid represented in format like this (E5E154C1-62D0-E311-940C-005056946CA6).
A Lookup which is a primary key from related entities is a same represented in Guid format along with its Primary name (string) attribute, as we can see in diagram.
- ParentCustomerId - Lookup Guid Column
- ParentCustomerIdName - Lookup Name (string)
How can we create Indexes in Dynamic CRM
For On-premises deployments of Dynamics CRM we can add SQL indexes as needed directly in SQL.
For CRM Online we are required to open a support ticket and make the request to Microsoft Support.
(We have to do a homework and be sure that the index will solve the issue and not leave database performance unaffected or, worse, slow down your CRM)
Enabling Quick Find Record Limits to “YES”
Understanding Quick Find in Dynamic CRM
Quick Find allows you to search for a record from a grid view.
Every entity have Quick Find Active view and can be configure from customization page.
When you open the configuration page of Quick Find Active view, we get an option to “Add Find Columns”.
We can tick on attributes which we think are beneficial for a quick search.
Don’t forget to save and Publish your changes.