Using Plugin:
I want to Retrieve 10 Million Records from CRM and Delete record from CRM in daily schedule.
Here, what type of approach if i used for face Timeout issue, Connection issue, Rollback and create log
Give me idea for handle Huge CRM data access defeat performance issue
Thanks
*This post is locked for comments
Hi DMat,
Recently i have done this kind of deletion using the console application/Schedular,
Using QueryExpression and paging you can retrieve all the records in single call and you can delete all the records in 1000 batch's. And if any error occur it will roll back as i have used Transaction Request. Below is my code
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Description;
using Microsoft.Crm.Sdk.Messages;
using System.Text;
using System.Threading.Tasks;
using System.ServiceModel;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
DateTime d1 = DateTime.Now;
Console.WriteLine(d1.ToString("dd-MM-yyyy hh:mm:ss"));
ClientCredentials _crmCredentials = new ClientCredentials();
Uri _organizationUri = new Uri("xxx-xx.api.crm.dynamics.com/.../Organization.svc");
_crmCredentials.UserName.UserName = "xx.xx.xx@xx.com";
_crmCredentials.UserName.Password = "*****";
using (OrganizationServiceProxy _serviceProxy = new OrganizationServiceProxy(_organizationUri, null, _crmCredentials, null))
{
try
{
_serviceProxy.Timeout = new TimeSpan(10, 0, 0);
List<Entity> retrieveAccount = RetrieveAccounttoDelete(_serviceProxy);
int totalConnection = (retrieveAccount.Count / 1000) + 1;
for (int updateConn = 0; updateConn < totalConnection; updateConn++)
{
#region Execute Multiple with Results
ExecuteTransactionRequest TransactionRequestDelete = null;
// Create an ExecuteTransactionRequest object.
TransactionRequestDelete = new ExecuteTransactionRequest()
{
// Create an empty organization request collection.
Requests = new OrganizationRequestCollection(),
//ReturnResponses = true
};
#endregion
var getAccountInBatch = from p in retrieveAccount.Take(retrieveAccount.Count)
select p;
// Add a UpdateRequest for each entity to the request collection.
foreach (Entity acc in getAccountInBatch)
{
EntityReference account = new EntityReference() { LogicalName = acc.LogicalName, Id = acc.Id };
DeleteRequest deleteRequest = new DeleteRequest { Target = account };
TransactionRequestDelete.Requests.Add(deleteRequest);
}
try
{
// Execute all the requests in the request collection using a single web method call.
var responseForCreateRecords = (ExecuteTransactionResponse)_serviceProxy.Execute(TransactionRequestDelete);
}
catch (FaultException<OrganizationServiceFault> ex)
{
if (ex.Detail.ErrorDetails.Contains("MaxBatchSize"))
{
int maxBatchSize = Convert.ToInt32(ex.Detail.ErrorDetails["MaxBatchSize"]);
if (maxBatchSize < TransactionRequestDelete.Requests.Count)
{
// Here you could reduce the size of your request collection and re-submit the ExecuteTransaction request.
// For this sample, that only issues a few requests per batch, we will just print out some info. However,
// this code will never be executed because the default max batch size is 1000.
}
}
throw;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
//get all the record which you want to delete
public static List<Entity> RetrieveAccounttoDelete(IOrganizationService service)
{
// Query using the paging cookie.
// Define the paging attributes.
// The number of records per page to retrieve.
int queryCount = 5000;
// Initialize the page number.
int pageNumber = 1;
List<Entity> Entities = new List<Entity>();
QueryExpression getAccount = new QueryExpression();
getAccount.EntityName = "account";
// Assign the pageinfo properties to the query expression.
getAccount.PageInfo = new PagingInfo();
getAccount.PageInfo.Count = queryCount;
getAccount.PageInfo.PageNumber = pageNumber;
// The current paging cookie. When retrieving the first page,
// pagingCookie should be null.
getAccount.PageInfo.PagingCookie = null;
while (true)
{
// Retrieve the page.
EntityCollection results = service.RetrieveMultiple(getAccount);
if (results.Entities != null)
{
Entities.AddRange(results.Entities);
}
// Check for more records, if it returns true.
if (results.MoreRecords)
{
// Increment the page number to retrieve the next page.
getAccount.PageInfo.PageNumber++;
// Set the paging cookie to the paging cookie returned from current results.
getAccount.PageInfo.PagingCookie = results.PagingCookie;
}
else
{
// If no more records are in the result nodes, exit the loop.
break;
}
}
return Entities;
}
}
}
What's wrong with bulk delete jobs? You can schedule them etc.. Not sure how long it's going to take for such a job to complete, though, - that you have 10 mln records per day to delete looks unusual.
Hi, My Code search 10M of records and delete from about 1k to 5k records every day.
When using SIS + Kinswaysoft Script, we need to any additional application except Visual studio
I would recommend using Kingswaysoft for the SSIS package.
I think your best bet is with an SSIS package, and of course doing this during non-business hours.
The 10M records are they from a single entity or multiple entities?
Basically based on the number of records of any single entity, you would need to do this in batches as the max number of records to retrieve is 5000.
Add a filter to your fetchXml, retrieve the first 5000, and run a delete command on those.
Do this process inside a loop in your SSIS package.
I would recommend using Kingswaysoft for the SSIS package.
Hope this helps.
Hi,
really delete 10Mio of record every day? Are you sure that the source of creation of those record answers really the Business needs?
What you could try is using an SSIS + Kinswaysoft Script to retrieve and delete those records. You can use multi thread but as I said it is not optimal/efficient.
Hi,
Is there any specific reason to use plugin to delete and retrieve 10 million data? Seems you can do this kind of jobs by using console application (Batch Jobs) or SSIS jobs. I personally prefer to use console application.
Hope this helps.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (