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 the one by one the Contact based on the ContactId supplied by the Order. To reduce taking one by one the Contact entity, we can achieve this 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 the 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 NoLockfeature 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);