In my previous blog post Dynamics crm data migration: Part-1 Oops I gave my opinion on how to tackle complex data migrations project-wise. This time I’ll get down to the nitty-gritty of any Dynamics crm data migration: optimizing the performance.

Should I use Scribe, Kingswaysoft SSIS or custom .NET import jobs? Which is faster?

It’s a question that is commonly asked in the CRM community. I always tell my clients that when it comes to performance… The choice of import tool just doesn’t matter that much. Use the one you are most comfortable and/or experienced with. The bottleneck of your performance will never be the tool used for importing, but mostly the way you are importing data into CRM.

The choice of import tool doesn’t matter that much.The bottleneck of your performance will never be the tool used for importing, but mostly the way you are importing data into CRM.

[bra_divider height=’10’]

There are a couple of golden rules we at Thrives live by when it comes to Data Imports:

1. All plugins & workflows should be deactivated during data migration
2. Transformations should be done before importing data

These rules might seem blunt at first but they do make sense and will improve your overall data migration performance drastically. Right about now you’re probably about to stop reading and ditch this post as “not useful in my scenario” but I urge to hang in there.

Forget Record Based, think Set Based.

For example, if you have a workflow which is creating custom Audit Records whenever a contact is created… the workflow can just as well be deactivated during the import process and a second import job can be created to create the audit records once all contacts have been imported. Again, think Set based: load your set of contacts, then your set of audit records.

This is probably what’s most difficult to grasp for us CRM developers, being used to writing plugins and workflows which always execute around a single record trigger such as Create, Update, Delete of a record. We would basically replace the following scenario:

Yeah, sure… How about an example?

Got it. Say you want to import 100.000 records in an entity Language Skill which indicates which languages your contact speaks and how good she is at it. In CRM we have three entities, Contact, Language and Language Knowledge which looks like this in CRM:

For the ERD fanatics amongst you, the ERD might look like this:

We also have a workflow that concatenates the Contact and Language fields of our Language Knowledge entity into the Name field.

The source file is an Excel file. Since I’m not going to use the standard import, I migrate the Excel File to a SQL Server table.

then becomes

Transform your data before importing

CRM Lookups

Rather than searching for each Contacts Guid in CRM using a RetrieveMultiple Query based on the contactnumber and searching for each Language’s Guid in CRM using another RetrieveMultiple Query we can just use SQL Servers capabilities to prefetch all required Guids.

We add three columns to our source table:

• crm_thr_languageid: will contain the guid of the language record
• crm_thr_contactid: will contain the guid of the contact record
• crm_thr_name: will contain the calculated name

alter table languagescorestoimport add crm_thr_languageid varchar(38)
alter table languagescorestoimport add crm_thr_contactid varchar(38) 
alter table languagescorestoimport add crm_thr_name varchar(150)

By now joining our source table with our CRM Filtered Views we are able to prefill the required GUIDS in our Source Table.

Other transformations

We still have the workflow functionality which needs to be replicated. Fortunately for us this one is very simple. While we are joining our source table with the required CRM tables, we might just as well retrieve the required fields from our CRM entities and perform the calculation in our SQL.

Our final query looks like this:

UPDATE score
SET
score.crm_thr_languageid = lang.thr_languageid
, score.crm_thr_contactid = contact.contactid
, score.crm_thr_name = contact.fullname + ' ' + lang.thr_name + ' ' + score.Score
FROM LanguageScoresToImport score
LEFT JOIN [MYLINKEDSERVER].[THRIVES_MSCRM].[dbo].[filteredContact] contact on score.OldContactNumber = contact.thr_oldcontactnumber
LEFT JOIN [MYLINKEDSERVER].[THRIVES_MSCRM].[dbo].[filteredthr_language] lang on score.LanguageCode = lang.thr_isocode

(Note the LEFT JOIN to make sure no data is omitted from the update)

Which gives us a source table enriched with the CRM guids and the concatenated name field. This can now be used to directly fill the Guids when performing our Create operations.

Because this update is executed in a single SQL JOIN statement we save ourselves the overhead of 100.000 Lookup calls to Contact entity and another 100.000 calls to the Language entity. That’s 200.00 RetrieveMultiple calls that do not need to be executed.

Even if RetrieveMultiple is a fast operation… 200.000 is an awful lot of calls to perform if not really needed. Let’s say one call only takes 50ms, we’re still saving 2,78 hours on select operations alone.

Taken into account that we just saved ourselves another additional 100.00 workflows from launching and I’d say the effort spent in creating a SQL Source Table and writing the SQL query is time and money well spent!

To summarize, it all comes down to reducing the number of overhead operations during the import itself.

Before Thrives’ Magic

After Thrives’ Magic

And what about CRM Online?

This scenario obviously works best for an On Premise CRM, but could just as well be applied for a CRM Online. When using CRM Online you need to extract the required entities and only their required fields into a local or Azure SQL Server (this can easily be done using the XrmToolbox, the OData Endpoint, Export to Excel functionality, …).

At Thrives we take great pride in delivering customer excellence and our Data Migration Expertise is one of our most asked for services. If you feel like you’re in need of some expertise or have any remarks, don’t hesitate to contact us.