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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.