Microsoft Dynamics CRM – Querying Data Using Late Binding versus Early Binding

200255412-001Although Microsoft recommends using early binding over late binding in Microsoft Dynamics CRM® 2011, my experience is that querying data is faster when using a late binding approach.

I demonstrate this by using the following three approaches:

  • Early binding
  • Late binding
  • A mix of early and late binding.

My tests were performed using an on-premise environment. The query was to extract over 300 thousand active accounts. (By default, Dynamics CRM 2011 restricts the number of records to 5000. In order to retrieve more, requires enabling the registry setting, TurnOffFetchThrottling – refer to http://support.microsoft.com/kb/911510 for more information)

Approach – All Records Time Elapsed
1 – Query all using Early Binding – CreateQuery<Account>() (seconds): 43 (milli): 382
2 – Query all using Late Binding – RetrieveMultiple (seconds): 23 (milli): 263
3 – Query all using Late & Early Binding – CreateQuery(“account”) (seconds): 37 (milli): 101
Approach – Specific Account  
4 – Query account using Early Binding – CreateQuery<Account>() (seconds): 1 (milli): 142
5 – Query  account  using Late Binding – RetrieveMultiple (seconds): 0 (milli): 203
6 – Query  account  Late & Early Binding – CreateQuery(“account”) (seconds): 0 (milli): 280
7 – Query account using Early Binding – Retrieve (seconds): 0 (milli): 406
8 – Query account using Late Binding – Retrieve (seconds): 0 (milli): 128

The above results show that the late binding, approach #2, when retrieving multiple records, is the fastest approach. The above results also highlight that, when retrieving, a specific account by its unique identifier, the late binding approach also out-performs the early binding.

In addition to the above tests, I also compared the performance of returning all columns versus only a few.

  • For early binding, the number of columns returned is controlled in the LINQ select statement

var accounts = from c in orgSvcContext.CreateQuery<Account>()
where (c.StateCode.Equals(“Active”))
select new Xrm.Account
{Id = c.Id,
new_Country = c.new_Country
// ADD MORE COLUMNS HERE};

  • Changing the number of columns did not affect performance that greatly.
  • For late binding, specifically, in the case of Approach #2, RetrieveMultiple, changing the number of columns greatly affects performance.
    • Controlling columns returned is accomplished by the QueryExpression Columnset property.
    • In my tests, when setting  Columnset to true, I actually encountered a timeout exception. (Note: the timeout depends on the number of records that are returned. In my case, returning over 300 thousand put too large a demand on the query.) The following error can be seen in the event viewer, located on the CRM server.

Log Name:      Application
Source:        MSCRMPlatform
Date:          8/12/2012 5:00:57 PM
Event ID:      17972
Task Category: None
Level:         Warning
Keywords:      Classic
User:          N/A
Computer:      crm2011server
Description:
Query execution time of 16.7 seconds exceeded the threshold of 10 seconds. Thread: 14; Database: crm_tst_MSCRM; Query: select
“account0″.Address1_FreightTermsCode as “address1_freighttermscode”
, “account0″.Telephone3 as “telephone3″
, “account0″.new_BookingSales as “new_bookingsales”
, “account0″.new_CompanyPeople as “new_companypeople”
….
….

Query all active accounts

Approach 1 – Query all using Early Binding – CreateQuery<Account>()

var accounts = (from c in orgSvcContext.CreateQuery<Account>()

where (c.StateCode.Equals(“Active”))
select new Xrm.Account
{
Id = c.Id,
new_Country = c.new_Country
}).ToList<Xrm.Account>();

Approach 2 – Query all using Late Binding – RetrieveMultiple

QueryExpression query = new QueryExpression();

query.EntityName = “account”;

query.ColumnSet = new ColumnSet(“ownerid”, “createdby”, “name”, “new_idse”, “new_country”, “new_stateprovince”, “address1_city”, “address1_postalcode”, “new_customertypeid”, “new_companyindustry”);
query.Criteria.AddCondition(“statecode”, ConditionOperator.Equal, 0);
List<Entity> results = new List<Entity>();
EntityCollection retrieved1 = _service.RetrieveMultiple(query);
results = retrieved1.Entities.ToList();
List<CrmAccount> result1 = new List<CrmAccount>();
var accounts = (from item in results
select new CrmAccount
{
accountid = (Guid)item["accountid"],
name = item.Contains(“name”) ? item["name"].ToString() : string.Empty
});

Approach 3 – Query all using Late & Early Binding – CreateQuery(“account”)

var query2 = (from c in orgSvcContext.CreateQuery(“account”)
where ((!c["statecode"].Equals(null) && (string)(c["statecode"]) == “Active”))
select new CrmAccount
{
accountid = (Guid)c["accountid"],
name = item.Contains(“name”) ? item["name"].ToString() : string.Empty
}).ToList();

Query specific account

Approach 4 – Query account using Early Binding – CreateQuery<Account>()

var accounts = (from c in orgSvcContext.CreateQuery<Account>()
where (c.Id.Equals(id) && c.StateCode.Value.Equals(0))
select new Xrm.Account
{
Id = c.Id,
new_Country = c.new_Country,
OwnerId = c.OwnerId,
Name = c.Name,
new_IDSE = c.new_IDSE,
new_StateProvince = c.new_StateProvince,
Address1_City = c.Address1_City,
Address2_PostalCode = c.Address2_PostalCode,
new_CustomerTypeID = c.new_CustomerTypeID,
new_CompanyIndustry = c.new_CompanyIndustry
}).ToList<Xrm.Account>();

Approach 5 – Query account using Late Binding – RetrieveMultiple

QueryExpression query = new QueryExpression();

query.EntityName = “account”;

query.ColumnSet = new ColumnSet(“ownerid”, “createdby”, “name”, “new_idse”, “new_country”, “new_stateprovince”, “address1_city”, “address1_postalcode”, “new_customertypeid”, “new_companyindustry”);
query.Criteria.AddCondition(“statecode”, ConditionOperator.Equal, 0);
query.Criteria.AddCondition(“accountid”, ConditionOperator.Equal, id);
List<Entity> results = new List<Entity>();
EntityCollection retrieved1 = _service.RetrieveMultiple(query);
results = retrieved1.Entities.ToList();
List<CrmAccount> result1 = new List<CrmAccount>();
var accounts = (from item in results
select new CrmAccount
{
accountid = (Guid)item["accountid"],
name = item.Contains(“name”) ? item["name"].ToString() : string.Empty
});

Approach 6 – Query account using Late & Early Binding – CreateQuery(“account”)

var query_join2 = (from c in orgSvcContext.CreateQuery(“account”)
where ((!c["statecode"].Equals(null)
&& (string)(c["statecode"]) == “Active”)
&& (c["accountid"].Equals(id)))
select new CrmAccount
{
accountid = (Guid)c["accountid"]
}).ToList();

Approach 7 – Query account using Late Binding – Retrieve

Entity entity = _serviceProxy.Retrieve(entityName, entityId, new ColumnSet(“name”));

Approach 8 – Query account using Early Binding – Retrieve

Account retrievedAccount = _serviceProxy.Retrieve(Account.EntityLogicalName, accountid,
new ColumnSet(“primarycontactid”)).ToEntity<Account>();

To learn more about Edgewater’s CRM offerings, click here.

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 )

Google+ photo

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

Connecting to %s