Problems with AllColumns in a QueryExpression
Scenario:
I recently delivered a CRM Code Review to a customer and have noticed majority of customizations using QueryExpression with ColumnSet object with true parameter.
The following issue may arise when retrieving all columns in a query expression:
Problem:
All Columns retrieves more columns from the FilteredView that you expect. For this reason, some queries can retrieve more data and take more time to execute leading to performance issues.
Consider a CRM organization with 50,000 records for testing purposes and the following sample code illustrating a query using all columns:
ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "address1_city";
condition.Operator = ConditionOperator.Equal;
condition.Values.Add("Dallas");
ColumnSet columns = new ColumnSet();
columns.AllColumns = true;
//ColumnSet columns = new ColumnSet(true); //Similar AllColumns = true;
QueryExpression query = new QueryExpression();
query.EntityName = "contact";
query.ColumnSet = columns;
query.Criteria.AddCondition(condition);
EntityCollection results = service.RetrieveMultiple(query);
foreach (Entity contact in results.Entities)
{
Console.WriteLine(contact["fullname"] + ": " + contact["emailaddress1"]);
}
Based on the code FullName and EmailAddress1 are the only information required to be displayed in this scenario. Reviewing the query in SQL Server:
exec sp_executesql N'select top 5001 "contact0".SpousesName as "spousesname", "contact0".EMailAddress3 as "emailaddress3", "contact0".Address3_Telephone3 as "address3_telephone3", "contact0".Address2_ShippingMethodCode as "address2_shippingmethodcode" , "contact0".PreferredServiceId as "preferredserviceid", "contact0".Address3_ShippingMethodCode as "address3_shippingmethodcode", "contact0".AnnualIncome as "annualincome", "contact0".Address2_Line2 as "address2_line2", "contact0".StageId as "stageid"
, "contact0".Telephone3 as "telephone3", "contact0".PreferredAppointmentDayCode as "preferredappointmentdaycode", "contact0".Address3_City as "address3_city", "contact0".CreatedBy as "createdby", "contact0".Address2_Line1 as "address2_line1" , "contact0".LastUsedInCampaign as "lastusedincampaign", "contact0".Address3_FreightTermsCode as "address3_freighttermscode", "contact0".Address3_Telephone2 as "address3_telephone2", "contact0".Pager as "pager", "contact0".EmployeeId as "employeeid"
, "contact0".TerritoryCode as "territorycode", "contact0".ParentCustomerId as "parentcustomerid", "contact0".ManagerName as "managername", "contact0".BirthDate as "birthdate", "contact0".NumberOfChildren as "numberofchildren" , "contact0".Address2_Latitude as "address2_latitude", "contact0".Address2_PostalCode as "address2_postalcode", "contact0".Home2 as "home2", "contact0".Address1_Latitude as "address1_latitude", "contact0".OwningUser as "owninguser"
, "contact0".AssistantPhone as "assistantphone", "contact0".MasterId as "masterid", "contact0".CreatedOnBehalfBy as "createdonbehalfby", "contact0".Company as "company", "contact0".MobilePhone as "mobilephone", "contact0".CustomerTypeCode as "customertypecode" , "contact0".Address3_AddressTypeCode as "address3_addresstypecode", "contact0".Address1_FreightTermsCode as "address1_freighttermscode", "contact0".Address1_Longitude as "address1_longitude", "contact0".Address1_AddressTypeCode as "address1_addresstypecode"
, "contact0".StatusCode as "statuscode", "contact0".YomiFullName as "yomifullname", "contact0".Aging90_Base as "aging90_base", "contact0".Address3_PostalCode as "address3_postalcode", "contact0".Address1_UPSZone as "address1_upszone" , "contact0".Address3_PrimaryContactName as "address3_primarycontactname", "contact0".FamilyStatusCode as "familystatuscode", "contact0".Address3_AddressId as "address3_addressid", "contact0".FirstName as "firstname", "contact0".Department as "department"
, "contact0".OriginatingLeadId as "originatingleadid", "contact0".Aging60 as "aging60", "contact0".TraversedPath as "traversedpath", "contact0".StateCode as "statecode", "contact0".Address2_FreightTermsCode as "address2_freighttermscode" , "contact0".Address1_Name as "address1_name", "contact0".YomiMiddleName as "yomimiddlename", "contact0".Description as "description", "contact0".Address3_UTCOffset as "address3_utcoffset", "contact0".ParticipatesInWorkflow as "participatesinworkflow"
, "contact0".Address1_Telephone3 as "address1_telephone3", "contact0".OwningTeam as "owningteam", "contact0".Address2_Telephone1 as "address2_telephone1", "contact0".GenderCode as "gendercode", "contact0".OwnerId as "ownerid" , "contact0".Address2_AddressTypeCode as "address2_addresstypecode", "contact0".Address1_Composite as "address1_composite", "contact0".Address1_UTCOffset as "address1_utcoffset", "contact0".WebSiteUrl as "websiteurl", "contact0".Aging60_Base as "aging60_base"
, "contact0".Address3_Line1 as "address3_line1", "contact0".Address1_County as "address1_county", "contact0".OwningBusinessUnit as "owningbusinessunit", "contact0".JobTitle as "jobtitle", "contact0".EntityImageId as "entityimageid" , "contact0".ProcessId as "processid", "contact0".Address1_Telephone2 as "address1_telephone2", "contact0".TimeZoneRuleVersionNumber as "timezoneruleversionnumber", "contact0".Address1_Fax as "address1_fax"
, "contact0".Address2_PostOfficeBox as "address2_postofficebox", "contact0".EMailAddress2 as "emailaddress2", "contact0".Address1_Line2 as "address1_line2", "contact0".Address3_Line2 as "address3_line2", "contact0".DoNotPostalMail as "donotpostalmail" , "contact0".Address2_UTCOffset as "address2_utcoffset", "contact0".ExchangeRate as "exchangerate", "contact0".CreatedOn as "createdon", "contact0".ImportSequenceNumber as "importsequencenumber", "contact0".Address2_Line3 as "address2_line3"
, "contact0".ManagerPhone as "managerphone", "contact0".Address2_AddressId as "address2_addressid", "contact0".OverriddenCreatedOn as "overriddencreatedon", "contact0".Address1_Telephone1 as "address1_telephone1" , "contact0".Address3_Composite as "address3_composite", "contact0".Address3_Fax as "address3_fax", "contact0".ChildrensNames as "childrensnames", "contact0".PreferredContactMethodCode as "preferredcontactmethodcode"
, "contact0".YomiLastName as "yomilastname", "contact0".Aging90 as "aging90", "contact0".DoNotBulkPostalMail as "donotbulkpostalmail", "contact0".EMailAddress1 as "emailaddress1", "contact0".DoNotBulkEMail as "donotbulkemail" , "contact0".CustomerSizeCode as "customersizecode", "contact0".Address1_City as "address1_city", "contact0".FullName as "fullname", "contact0".PreferredAppointmentTimeCode as "preferredappointmenttimecode", "contact0".Address3_Latitude as "address3_latitude"
, "contact0".TransactionCurrencyId as "transactioncurrencyid", "contact0".PaymentTermsCode as "paymenttermscode", "contact0".Address3_Name as "address3_name", "contact0".Aging30 as "aging30", "contact0".LeadSourceCode as "leadsourcecode" , "contact0".Merged as "merged", "contact0".Address2_Telephone3 as "address2_telephone3", "contact0".Address1_ShippingMethodCode as "address1_shippingmethodcode", "contact0".ModifiedBy as "modifiedby", "contact0".FtpSiteUrl as "ftpsiteurl"
, "contact0".PreferredSystemUserId as "preferredsystemuserid", "contact0".Address2_Telephone2 as "address2_telephone2", "contact0".Address1_AddressId as "address1_addressid" , "contact0".Address3_StateOrProvince as "address3_stateorprovince", "contact0".Address3_Telephone1 as "address3_telephone1", "contact0".NickName as "nickname", "contact0".Address1_PostOfficeBox as "address1_postofficebox"
, "contact0".PreferredEquipmentId as "preferredequipmentid", "contact0".AssistantName as "assistantname", "contact0".Address2_Country as "address2_country", "contact0".ModifiedOn as "modifiedon", "contact0".ExternalUserIdentifier as "externaluseridentifier"
, "contact0".Address2_Name as "address2_name", "contact0".CreditOnHold as "creditonhold", "contact0".Address3_Longitude as "address3_longitude", "contact0".AccountRoleCode as "accountrolecode", "contact0".Address3_Country as "address3_country" , "contact0".DoNotPhone as "donotphone", "contact0".Address3_UPSZone as "address3_upszone", "contact0".Address3_County as "address3_county", "contact0".ContactId as "contactid", "contact0".DoNotFax as "donotfax", "contact0".Aging30_Base as "aging30_base"
, "contact0".ModifiedOnBehalfBy as "modifiedonbehalfby", "contact0".Salutation as "salutation", "contact0".Suffix as "suffix", "contact0".Address1_PrimaryContactName as "address1_primarycontactname", "contact0".Business2 as "business2" , "contact0".UTCConversionTimeZoneCode as "utcconversiontimezonecode", "contact0".GovernmentId as "governmentid", "contact0".Address2_PrimaryContactName as "address2_primarycontactname", "contact0".Address2_StateOrProvince as "address2_stateorprovince"
, "contact0".DoNotSendMM as "donotsendmm", "contact0".AnnualIncome_Base as "annualincome_base", "contact0".Address1_Country as "address1_country", "contact0".LastName as "lastname", "contact0".Address2_City as "address2_city" , "contact0".DoNotEMail as "donotemail", "contact0".Address3_PostOfficeBox as "address3_postofficebox", "contact0".Address2_Longitude as "address2_longitude", "contact0".Address1_StateOrProvince as "address1_stateorprovince"
, "contact0".YomiFirstName as "yomifirstname", "contact0".Telephone1 as "telephone1", "contact0".Address1_Line1 as "address1_line1", "contact0".Address2_Composite as "address2_composite", "contact0".Address2_County as "address2_county" , "contact0".ShippingMethodCode as "shippingmethodcode", "contact0".CreditLimit as "creditlimit", "contact0".Anniversary as "anniversary", "contact0".Telephone2 as "telephone2", "contact0".EducationCode as "educationcode"
, "contact0".HasChildrenCode as "haschildrencode", "contact0".Address2_Fax as "address2_fax", "contact0".IsBackofficeCustomer as "isbackofficecustomer", "contact0".Fax as "fax", "contact0".Address1_Line3 as "address1_line3" , "contact0".CreditLimit_Base as "creditlimit_base", "contact0".Address2_UPSZone as "address2_upszone", "contact0".DefaultPriceLevelId as "defaultpricelevelid", "contact0".MiddleName as "middlename", "contact0".Address1_PostalCode as "address1_postalcode"
, "contact0".Address3_Line3 as "address3_line3", "contact0".Callback as "callback", "contact0".EntityImage_Timestamp as "entityimage_timestamp", "contact0".EntityImage_URL as "entityimage_url", "contact0".PreferredServiceIdName as "preferredserviceidname"
, "contact0".CreatedByYomiName as "createdbyyominame", "contact0".CreatedByName as "createdbyname", "contact0".ParentCustomerIdName as "parentcustomeridname", "contact0".ParentCustomerIdYomiName as "parentcustomeridyominame" , "contact0".ParentCustomerIdType as "parentcustomeridtype", "contact0".MasterContactIdName as "mastercontactidname", "contact0".MasterContactIdYomiName as "mastercontactidyominame", "contact0".CreatedOnBehalfByYomiName as "createdonbehalfbyyominame"
, "contact0".CreatedOnBehalfByName as "createdonbehalfbyname", "contact0".OriginatingLeadIdName as "originatingleadidname", "contact0".OriginatingLeadIdYomiName as "originatingleadidyominame", "contact0".OwnerIdType as "owneridtype" , "contact0".OwnerIdName as "owneridname", "contact0".OwnerIdYomiName as "owneridyominame", "contact0".TransactionCurrencyIdName as "transactioncurrencyidname", "contact0".ModifiedByYomiName as "modifiedbyyominame"
, "contact0".ModifiedByName as "modifiedbyname", "contact0".PreferredSystemUserIdYomiName as "preferredsystemuseridyominame", "contact0".PreferredSystemUserIdName as "preferredsystemuseridname", "contact0".PreferredEquipmentIdName as "preferredequipmentidname"
, "contact0".ModifiedOnBehalfByYomiName as "modifiedonbehalfbyyominame", "contact0".ModifiedOnBehalfByName as "modifiedonbehalfbyname", "contact0".DefaultPriceLevelIdName as "defaultpricelevelidname"
from Contact as "contact0"
where (("contact0".Address1_City = @Address1_City0)) order by
"contact0".ContactId asc',N'@Address1_City0 nvarchar(200)',@Address1_City0=N'Dallas'
A considerable amount of columns are requested. Additionally, other tables are included in this query (Right Outer Join). Results against a organization with 50,000 contacts:
(2012 row(s) affected)
Table 'Workfile' . Scan count 0, logical reads 0,
Table 'Worktable' . Scan count 0, logical reads 0,
Table 'ContactBase' . Scan count 0, logical reads 9052,
Table 'CustomerAddressBase' . Scan count 1, logical reads 17647,
Table 'LeadBase' . Scan count 1, logical reads 2,
Table 'EquipmentBase' . Scan count 1, logical reads 0,
Table 'ImageDescriptor' . Scan count 1, logical reads 0,
Table 'SystemUserBase' . Scan count 5, logical reads 10,
Table 'PriceLevelBase' . Scan count 1, logical reads 2,
Table 'ServiceBase' . Scan count 1, logical reads 0,
Table 'TransactionCurrencyBase' . Scan count 1, logical reads 2,
Table 'OwnerBase' . Scan count 1, logical reads 2,
Recommendation:
Modify ColumnSet to retrieve only required columns.
ColumnSet columns = new ColumnSet();
columns.AddColumns("fullname", "emailaddress1");
//columns.AllColumns = true;
//ColumnSet columns = new ColumnSet(true); Similar to columns.AllColumns = true
In this example, the query retrieved data from only two tables:
exec sp_executesql N'select top 5001 "contact0".FullName as "fullname"
, "contact0".EMailAddress1 as "emailaddress1"
, "contact0".ContactId as "contactid"
from Contact as "contact0"
where (("contact0".Address1_City = @Address1_City0)) order by
"contact0".ContactId asc',N'@Address1_City0 nvarchar(200)',@Address1_City0=N'Dallas'
The difference in the query will reflect in the results. Less logical reads and less tables in the execution plan:
(2012 row(s) affected)
Table 'ContactBase' . Scan count 0, logical reads 8159,
Table 'CustomerAddressBase' . Scan count 1, logical reads 5307,
Conclusion:
1. Avoid using ColumnSet in a QueryExpression to retrieve all columns.
2. Explicitly define your columns in the ColumnSet.
3. ColumnSet using false parameter in a QueryExpression will retrieve only the record Guid.
Next Steps:
1. Consider using PageInfo class in QueryExpression to limit number of records retrieved.
2. Validate if SQL Indexes can be helpful in your queries.