Você precisa de todas as colunas em uma QueryExpression?
Essa semana trabalhei em um cliente e notei que suas customizações (plugins, webservices) possuíam em grande maioria as queries (QueryExpression) construídas com o objeto ColumnSet definindo todas as colunas na pesquisa.
Em várias situações, as queries eram utilizadas com o propósito de verificar apenas o valor de um ou dois campos dos registros.
Vamos ver abaixo a diferença de performance quando temos uma QueryExpression com o objeto ColumnSet requisitando todos atributos e outro cenário requisitando somente os necessários.
Considere o seguinte trecho de código de exemplo* executado em uma organização com o Sample Data instalado:
ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "address1_city";
condition.Operator = ConditionOperator.Equal;
condition.Values.Add("Redmond");
ColumnSet columns = new ColumnSet(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"]);
}
Repare que preciso apenas do Nome Completo e Endereço de E-mail dos contatos onde a cidade é igual a Redmond. Vamos ver como essa QueryExpression foi traduzida no SQL Server e respectivos números:
exec sp_executesql N'select top 5001 "contact0".SpousesName as "spousesname", "contact0".EMailAddress3 as "emailaddress3", "contact0".Address2_ShippingMethodCode as "address2_shippingmethodcode", "contact0".PreferredServiceId as "preferredserviceid"
, "contact0".AnnualIncome as "annualincome", "contact0".Address2_Line2 as "address2_line2", "contact0".Fax as "fax", "contact0".Telephone3 as "telephone3", "contact0".PreferredAppointmentDayCode as "preferredappointmentdaycode"
, "contact0".Address2_StateOrProvince as "address2_stateorprovince", "contact0".CreatedBy as "createdby", "contact0".Address2_Line1 as "address2_line1", "contact0".LastUsedInCampaign as "lastusedincampaign", "contact0".YomiLastName as "yomilastname"
, "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".Address1_AddressTypeCode as "address1_addresstypecode", "contact0".OwningTeam as "owningteam", "contact0".Address2_PostalCode as "address2_postalcode"
, "contact0".UTCConversionTimeZoneCode as "utcconversiontimezonecode", "contact0".OwningUser as "owninguser", "contact0".AssistantPhone as "assistantphone", "contact0".MasterId as "masterid", "contact0".CreatedOnBehalfBy as "createdonbehalfby"
, "contact0".Address1_Latitude as "address1_latitude", "contact0".MobilePhone as "mobilephone", "contact0".CustomerTypeCode as "customertypecode", "contact0".ManagerPhone as "managerphone", "contact0".Address1_FreightTermsCode as "address1_freighttermscode"
, "contact0".Address1_Longitude as "address1_longitude", "contact0".Address2_Telephone2 as "address2_telephone2", "contact0".StatusCode as "statuscode", "contact0".YomiFullName as "yomifullname", "contact0".Aging90_Base as "aging90_base"
, "contact0".FamilyStatusCode as "familystatuscode", "contact0".FirstName as "firstname", "contact0".Address2_Latitude as "address2_latitude", "contact0".Department as "department", "contact0".OriginatingLeadId as "originatingleadid"
, "contact0".Aging60 as "aging60", "contact0".StateCode as "statecode", "contact0".Address2_FreightTermsCode as "address2_freighttermscode", "contact0".Address1_Name as "address1_name", "contact0".YomiMiddleName as "yomimiddlename"
, "contact0".ModifiedOnBehalfBy as "modifiedonbehalfby", "contact0".Description as "description", "contact0".EducationCode as "educationcode", "contact0".Address1_Telephone3 as "address1_telephone3", "contact0".Address2_Telephone1 as "address2_telephone1"
, "contact0".ImportSequenceNumber as "importsequencenumber", "contact0".GenderCode as "gendercode", "contact0".OwnerId as "ownerid", "contact0".Address2_AddressTypeCode as "address2_addresstypecode", "contact0".Address1_UTCOffset as "address1_utcoffset"
, "contact0".CreditLimit_Base as "creditlimit_base", "contact0".Aging60_Base as "aging60_base", "contact0".Address1_County as "address1_county", "contact0".OwningBusinessUnit as "owningbusinessunit", "contact0".JobTitle as "jobtitle"
, "contact0".Address1_Telephone2 as "address1_telephone2", "contact0".TimeZoneRuleVersionNumber as "timezoneruleversionnumber", "contact0".Address1_Fax as "address1_fax", "contact0".Address2_PostOfficeBox as "address2_postofficebox", "contact0".Address1_Line2 as "address1_line2"
, "contact0".WebSiteUrl as "websiteurl", "contact0".DoNotPostalMail as "donotpostalmail", "contact0".Address2_UTCOffset as "address2_utcoffset", "contact0".ExchangeRate as "exchangerate", "contact0".CreatedOn as "createdon"
, "contact0".EMailAddress2 as "emailaddress2", "contact0".Address2_Line3 as "address2_line3", "contact0".Address2_AddressId as "address2_addressid", "contact0".OverriddenCreatedOn as "overriddencreatedon", "contact0".AnnualIncome_Base as "annualincome_base"
, "contact0".Address1_Telephone1 as "address1_telephone1", "contact0".ChildrensNames as "childrensnames", "contact0".PreferredContactMethodCode as "preferredcontactmethodcode", "contact0".Aging90 as "aging90", "contact0".DoNotBulkPostalMail as "donotbulkpostalmail"
, "contact0".EMailAddress1 as "emailaddress1", "contact0".DoNotBulkEMail as "donotbulkemail", "contact0".CustomerSizeCode as "customersizecode", "contact0".Address1_StateOrProvince as "address1_stateorprovince", "contact0".FullName as "fullname"
, "contact0".PreferredAppointmentTimeCode as "preferredappointmenttimecode", "contact0".TransactionCurrencyId as "transactioncurrencyid", "contact0".PaymentTermsCode as "paymenttermscode", "contact0".Aging30 as "aging30", "contact0".LeadSourceCode as "leadsourcecode"
, "contact0".Merged as "merged", "contact0".Address2_County as "address2_county", "contact0".Address1_ShippingMethodCode as "address1_shippingmethodcode", "contact0".FtpSiteUrl as "ftpsiteurl", "contact0".PreferredSystemUserId as "preferredsystemuserid"
, "contact0".Address1_AddressId as "address1_addressid", "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".ParticipatesInWorkflow as "participatesinworkflow", "contact0".AccountRoleCode as "accountrolecode", "contact0".Address2_Telephone3 as "address2_telephone3", "contact0".DoNotPhone as "donotphone", "contact0".ContactId as "contactid"
, "contact0".DoNotFax as "donotfax", "contact0".Aging30_Base as "aging30_base", "contact0".Address1_UPSZone as "address1_upszone", "contact0".Suffix as "suffix", "contact0".Address1_PrimaryContactName as "address1_primarycontactname"
, "contact0".Salutation as "salutation", "contact0".ModifiedBy as "modifiedby", "contact0".GovernmentId as "governmentid", "contact0".Address2_PrimaryContactName as "address2_primarycontactname", "contact0".DoNotSendMM as "donotsendmm"
, "contact0".Address2_Longitude as "address2_longitude", "contact0".Address1_Country as "address1_country", "contact0".LastName as "lastname", "contact0".Address2_City as "address2_city", "contact0".DoNotEMail as "donotemail"
, "contact0".Address1_City as "address1_city", "contact0".YomiFirstName as "yomifirstname", "contact0".Telephone1 as "telephone1", "contact0".Address1_Line1 as "address1_line1", "contact0".ShippingMethodCode as "shippingmethodcode"
, "contact0".CreditLimit as "creditlimit", "contact0".Anniversary as "anniversary", "contact0".Telephone2 as "telephone2", "contact0".HasChildrenCode as "haschildrencode", "contact0".Address2_Fax as "address2_fax"
, "contact0".IsBackofficeCustomer as "isbackofficecustomer", "contact0".Address1_Line3 as "address1_line3", "contact0".Address2_UPSZone as "address2_upszone", "contact0".DefaultPriceLevelId as "defaultpricelevelid", "contact0".MiddleName as "middlename"
, "contact0".Address1_PostalCode as "address1_postalcode", "contact0".new_customfield as "new_customfield", "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".MasterContactIdYomiName as "mastercontactidyominame", "contact0".MasterContactIdName as "mastercontactidname"
, "contact0".CreatedOnBehalfByYomiName as "createdonbehalfbyyominame", "contact0".CreatedOnBehalfByName as "createdonbehalfbyname", "contact0".OriginatingLeadIdName as "originatingleadidname", "contact0".OriginatingLeadIdYomiName as "originatingleadidyominame", "contact0".ModifiedOnBehalfByYomiName as "modifiedonbehalfbyyominame"
, "contact0".ModifiedOnBehalfByName as "modifiedonbehalfbyname", "contact0".OwnerIdType as "owneridtype", "contact0".OwnerIdName as "owneridname", "contact0".OwnerIdYomiName as "owneridyominame", "contact0".TransactionCurrencyIdName as "transactioncurrencyidname"
, "contact0".PreferredSystemUserIdName as "preferredsystemuseridname", "contact0".PreferredSystemUserIdYomiName as "preferredsystemuseridyominame", "contact0".PreferredEquipmentIdName as "preferredequipmentidname", "contact0".ModifiedByYomiName as "modifiedbyyominame", "contact0".ModifiedByName as "modifiedbyname"
, "contact0".DefaultPriceLevelIdName as "defaultpricelevelidname" from Contact as "contact0" where (("contact0".Address1_City = @Address1_City0)) order by
"contact0".ContactId asc',N'@Address1_City0 nvarchar(7)',@Address1_City0=N'Redmond'
Uma grande quantidade desnecessária de colunas são retornadas. Adicionalmente, várias outras tabelas são incluídas nessa pesquisa (Left Outer Join) gerando cerca de 68 logical reads.
(2 row(s) affected)
Table 'OwnerBase'. Scan count 1, logical reads 5, physical reads 0, …
Table 'TransactionCurrencyBase'. Scan count 1, logical reads 5, physical reads 0, …
Table 'SystemUserBase'. Scan count 5, logical reads 30, physical reads 0, …
Table 'ServiceBase'. Scan count 1, logical reads 0, physical reads 0, …
Table 'PriceLevelBase'. Scan count 1, logical reads 5, physical reads 0, …
Table 'EquipmentBase'. Scan count 1, logical reads 0, physical reads 0, …
Table 'LeadBase'. Scan count 0, logical reads 0, physical reads 0, …
Table 'ContactBase'. Scan count 0, logical reads 10, physical reads 0, …
Table 'CustomerAddressBase'. Scan count 1, logical reads 9, physical reads 0, …
Table 'ContactExtensionBase'. Scan count 0, logical reads 4, physical reads 0, …
Dessa vez, vamos executar o mesmo código de exemplo, porém, informando as colunas necessárias para este exemplo no ColumnSet:
ColumnSet columns = new ColumnSet();
columns.AddColumns("fullname", "emailaddress1");
QueryExpression query = new QueryExpression();
query.EntityName = "contact";
query.ColumnSet = columns;
query.Criteria.AddCondition(condition);
A diferença é notável na query e nos respectivos números (reduzimos de 10 tabelas para 3 e de 68 logical reads para apenas 19):
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(7)',@Address1_City0=N'Redmond'
(2 row(s) affected)
Table 'ContactExtensionBase'. Scan count 0, logical reads 4, physical reads 0, …
Table 'ContactBase'. Scan count 0, logical reads 10, physical reads 0, …
Table 'CustomerAddressBase'. Scan count 1, logical reads 5, physical reads 0, …
Resumo: No exemplo acima, notamos melhor performance nas queries criadas via QueryExpression quando utilizamos o objeto ColumnSet para retornar somente os campos necessários para este cenário.
Código de Exemplo: fornecido apenas como ilustração, sem qualquer tipo de garantia ou suporte da Microsoft.