Dynamics CRM: QueryExpression and how to boost the performance!

In my company where I work, we have a custom program that will process big load data. Per running, it can process around 50.000 records. The average finished can be more than 3 hours ++. After I check, one of the things that make this slowness is because of the retrieving process.

var query = new QueryExpression("gent_order")
{
	ColumnSet = new ColumnSet("new_orderid", "new_contactid", "new_totalamount")
};
query.Criteria.AddCondition("new_campaignid", ConditionOperator.Equal, campaignId);

var result = service.RetrieveMultiple(query);
var orders = result.Entities.ToArray();

foreach (var order in orders)
{
	var contactId = order.GetAttributeValue("new_contactid");
	if (contactId == null) continue;

	var contact = service.Retrieve("contact", contactId, new ColumnSet("fullname"));
	//Action to do
}

The problem with that code is we retrieve the Orders, but separately retrieve the Contact entity. Using this way, the program needs to retrieve one by one the Contact based on ContactId that supplied by the Order. For reducing taking one by one the Contact entity, we can achieve it using AddLink (join).

var query = new QueryExpression("gent_order")
{
	ColumnSet = new ColumnSet("new_orderid", "new_contactid", "new_totalamount")
};
query.Criteria.AddCondition("new_campaignid", ConditionOperator.Equal, campaignId);

var contactLink = query.AddLink("contact", "new_contactid", "contactid");
contactLink.Columns = new ColumnSet("fullname");
contactLink.EntityAlias = "ca";

var result = service.RetrieveMultiple(query);
var orders = result.Entities.ToArray();

foreach (var order in orders)
{
	//Action to do
}

With this code, we just need one retrieving process to the database and will cut a lot of time. But when you want to get data from join table attribute value, you must use AliasValue like this:

var fullNameAlias = order.GetAttributeValue("ca.fullname");
var fullName = fullNameAlias != null ? (string) fullNameAlias.Value : "";

The other way to increase the speed is using the NoLock feature on QueryExpression. If you enabled this, it means that you are using Dirty Read. How to use it is very easy:

var query = new QueryExpression("gent_order")
{
	ColumnSet = new ColumnSet("new_orderid", "new_contactid", "new_totalamount"),
	NoLock = true
};
query.Criteria.AddCondition("new_campaignid", ConditionOperator.Equal, campaignId);

Advertisement