Wednesday, December 14, 2016

Lookup is slow in Microsoft Dynamic CRM.

Problem Statement :

Lookup is unresponsive and painfully slow in Dynamic CRM.

Objective :

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”

Navigate to Settings -> Administration -> System Setting page.

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.

How to create a custom Lookup control used in web resource in Microsoft Dynamic CRM (MS CRM)?

Vipin Jaiswal

1 comment:

Anonymous said...

I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Big Data Hadoop and Spark Developer, kindly contact us
MaxMunus Offer World Class Virtual Instructor led training on MS Dynamic CRM. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us.
Sangita Mohanty
Skype id: training_maxmunus
Ph:(0) 9738075708 / 080 - 41103383