SBX - Search With Button

SBX - Forum Post Title

Performance - How to Delete CRM 2011 records in bulk quickly

Microsoft Dynamics CRM Forum

AmarT asked a question on 29 Apr 2019 1:07 AM

Question Status

Suggested Answer

Hi,

We installed CRM 2011 in 2013 and had been using it since then. Now, as the number of  records got increased tremendously, as part of cleanup, we want to delete the old records. The tables have parent-child relationship, restrict delete relationship and dependency on same tables as well (i.e., sort of parent-child relationship within the table).

With these complications, for most of the tables, on an average, the CRM API based deletion is taking about 20 seconds to delete a record.

Even with CRM tool, manually, it is taking almost the same time.

For bulk deletion of records from CRM tables, is there any other safe way of quick deletion ?

Also, can we delete the records using SQL Delete operation ? What are the problems that we might face if we delete this way ?

Reply
Lu Hao responded on 29 Apr 2019 3:04 AM
My Badges
Suggested Answer

Hi AmarT,

We first use View or Advanced Find to list the records we want to delete, then export the records to Excel and delete the records in bulk in the Excel file, finally import it into CRM.

Please refer to these two articles:

Hope this helps.

Best Regards,

Lu Hao

Reply
David Jennaway responded on 29 Apr 2019 9:29 AM
My Badges

Any supported deletion mechanism (UI, bulk delete, CRM API) will need to check the relationships, so will take a similar amount of time. 

I can think of 3 possible ways to improve this:

  1. 20 seconds to delete does seem excessive. You may be able to improve this by adding indexes on the relationship columns. You could either just add the indexes and then check the performance, or you could use SQL tracing to capture the SQL statement(s), and replay them in SQL Management Studio. 
  2. Temporarily change the relationships to Remove Link, though you'll need to be certain that you're deleting the right records
  3. It is possible to use SQL Delete, though necessarily it's unsupported. For custom entities this is relatively straightforward as there won't be any other tables to watch for. As it's CRM 2011, you'll need to delete from the ExtensionBase and Base tables, and you should do this as a SQL transaction. If you have any related data (activities, workflows etc), you should clear this up as well to avoid invalid reference errors

Or, is it viable to consider upgrading to CRM 2013, then deleting the records. In CRM 2013 the ExtensionBase and Base tables are merged into one table, which may well improve delete performance (by reducing locks, and simplifying the query optimisation). However, it's difficult to predict how long the upgrade would take to run

Reply
Lu Hao responded on 29 Apr 2019 3:04 AM
My Badges
Suggested Answer

Hi AmarT,

We first use View or Advanced Find to list the records we want to delete, then export the records to Excel and delete the records in bulk in the Excel file, finally import it into CRM.

Please refer to these two articles:

Hope this helps.

Best Regards,

Lu Hao

Reply

SBX - Two Col Forum

SBX - Migrated JS