Olá pessoal,
A performance das queries (consultas) que fazemos no Dynamics CRM/365 para mim sempre foi algo que nunca cheguei a um consenso para determinar qual realmente é a mais eficaz. Principalmente quando estamos trabalhando com uma grande quantidade de dados/registros. Assim decidi, fazer alguns testes e pesquisas e escrever sobre os resultados!
Para começar este post irá abordar apenas consultas para Dynamics 365 Online. Desta forma, não estou considerando consultas à views do banco de dados (pois no Online não podemos fazer).
Ao iniciar as pesquisar achei interessante o fato de existir pouco material sobre como consultar grandes volumes de dados usando algo mais perfomático.
Encontrei sim, bastante informação sobre ExecuteMultipleRequest, mas sempre focado em Create/Update/Deletes. Por mais que seja possível de ser utilizado também para um objeto do tipo “retrieve”, o controle de paginação (page cookies) não funcionou em nenhum dos testes que fiz.
O único material que encontrei que realmente vai de encontro que o que procurava foi o PFE Core Library for Dynamics CRM. Esta biblioteca faz uso do Parallel.For para criar threads e fazer multi-chamadas. Irei falar mais disso à seguir.
Bom, o SDK do Dynamics diz que podemos fazer queries usando as seguintes técnicas/formas:
- FetchXML
- QueryExpression
- LINQ (Early ou Late Bound)
Com isso, criei uma organização do Dynamics 365 Online e importei 100.000 registros de Conta(account).
No Visual Studio criei uma console application para testar cada técnica. Na minha primeira execução. Consultei apenas 10.000 registros, porém, retornando todas os atributos de cada registro. Para ter melhor número para representar a média. Repeti programaticamente a execução de cada técnica de forma isolada por 100 vezes. Com isso, acredito que temos um resultado mais confiável e não viciado.
*Apenas para ficar claro, que fiz ao máximo (dentro das minhas capacidades de programador que são muitas! rs) para que cada diferente tipo de consulta seguisse as mesmas condições, para que o resultado pudesse realmente ser comparado. Outro ponto importante, é que não existe reaproveitamento de conexões ou uso da mesma execução para testar todos os tipos de consultas de uma só vez. Cada checagem foi feita independente das demais.
Vamos ao resultado… Huuuum foi decepcionante:
Praticamente todas as técnicas demoraram em média 1 minutos para retornar 10.000 resultados! Ai devemos lembrar de nossa primeira aula de banco de dados… NUNCA FAÇA UMA CONSULTA USANDO SELECT * FROM! A própria MS não recomenda consultas sem especificar os atributos.
Sendo assim, comecei a pesquisar apenas por dois atributos (name e accountid). E repeti a mesma quantidade de dados e execuções (10.000 x 100 x técnica):
O resultado é animador! De quase 1 minuto para 4 segundos em todas as técnicas!
Após este promissor resultado, decidi retornar todos registros de conta (100.140) que eu tenho nesta organização. Porém desta fez, além das demais formas de consulta que já estava testando, adicionei mais uma. Fiz uso da biblioteca PFE que consiste no uso de Parallel.For. O número de repetições continua sendo 100. Vejam os resultados:
Boom! Estes resultados realmente mudaram minha percepção do que eu achava que era mais ou menos perfomático!!!
FetchXML – PFE mostrou o mais rápido de todos com apenas 17 segundos, foi possível recuperar 100.140 registros!
Mas até ai, não foi uma grande surpresa. Para mim, LINQ (Late Bound) ser mais rápido que QueryExpression e ainda LINQ (Early Bound) também ser mais rápido que FetchXML foram para lá de uma enorme surpresa! Eu sempre acreditei que FetchXML e QueryExpression teriam uma melhor desempenho em comparação com o LINQ, mas não foi o que os resultados mostraram.
Se formos ver ao “pé da letra”, a diferença do LINQ (Early Bound) para a QueryExpression é de 0,1 segundos, quase imperceptível. E levando em conta que LINQ certamente é a forma mais simples de se manipular dados no mundo .NET vale muito a pena utilizá-lo sempre!
De qualquer forma, todas as técnicas mostraram-se altamente eficazes a variação entre a mais rápida (FetchXML – PFE) com a mais lenta (FetchXML) é de apenas 2,1 segundo (11 % de variação). Ainda sim, a preferência por A ou B pode ser utiliza em cenários com pouco volume de dados.
Fiz uma progressão levando em consideração estes resultados para o volume de 1 milhão de registros (claro, progressão nunca é exata, mas não custa ver). O resultado é retornado entre 2:50 à 3:10 minutos para as mesmas formas de consultas utilizadas anteriormente.
Para quem gosta de um pouco de código, segue o que utilizei para estes testes. Primeiro a Console Application:
using Microsoft.Pfe.Xrm;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
namespace Performance.Retrieve
{
class Program
{
static string queryType;
static int count;
static IOrganizationService orgService;
static void Main(string[] args)
{
queryType = ConfigurationManager.AppSettings[0].ToString();
StringBuilder log = new StringBuilder();
count = Convert.ToInt32(ConfigurationManager.AppSettings[1].ToString());
DateTime start = DateTime.Now;
log.Append(queryType + " Retrieving " + count + " records --- ");
switch (queryType)
{
case "LinqEarly":
LinqEarlyBound();
break;
case "LinqLate":
LinqLateBound();
break;
case "QueryExpression":
QueryExpression();
break;
case "FetchXml":
FetchXml();
break;
case "FetchXmlParallel":
FetchParallel();
break;
}
//log.AppendLine("End time: " + DateTime.Now);
//log.AppendLine("Execution time: " + (DateTime.Now - start));
log.Append((DateTime.Now - start).ToString());
Console.WriteLine(log.ToString());
Log(queryType, log);
//Console.ReadKey();
}
private static void FetchXml()
{
// Get CRM Connection
orgService = new CrmConnection().GetConnection();
string fetch2 = "<fetch mapping='logical' no-lock='true'>";
fetch2 += "<entity name='account'>";
// fetch2 += "<all-attributes />";
fetch2 += "<attribute name='name'/>";
fetch2 += "<attribute name='accountid'/>";
fetch2 += "<order attribute='accountid'/>";
fetch2 += "</entity>";
fetch2 += "</fetch>";
// Define the fetch attributes.
// Set the number of records per page to retrieve.
int fetchCount = 5000;
// Initialize the page number.
int pageNumber = 1;
// Specify the current paging cookie. For retrieving the first page,
// pagingCookie should be null.
string pagingCookie = null;
while (true)
{
if ((fetchCount * pageNumber) > count)
{
break;
}
// Build fetchXml string with the placeholders.
string xml = CreateXml(fetch2, pagingCookie, pageNumber, fetchCount);
FetchExpression expression = new FetchExpression(xml);
var results = orgService.RetrieveMultiple(expression);
// * Build up results here *
// Check for morerecords, if it returns 1.
if (results.MoreRecords)
{
// Increment the page number to retrieve the next page.
pageNumber++;
pagingCookie = results.PagingCookie;
}
else
{
// If no more records in the result nodes, exit the loop.
break;
}
}
}
private static void LinqEarlyBound()
{
// Get CRM Connection
orgService = new CrmConnection().GetConnection();
// Declare Context
using (var context = new ServiceContext(orgService))
{
// Retrieve Account records
var accounts = (from m in context.AccountSet
orderby m.AccountId
select new
{
m.Name,
m.AccountId
}
)
.Take(count);
var c = accounts.ToList().Count;
}
}
private static void LinqLateBound()
{
// Get CRM Connection
orgService = new CrmConnection().GetConnection();
OrganizationServiceContext context = new OrganizationServiceContext(orgService);
// Retrieve Account records
var accounts = (from m in context.CreateQuery("account")
orderby m.GetAttributeValue<Guid>("accountid")
select new
{
name = m.GetAttributeValue<string>("name"),
accountid = m.GetAttributeValue<Guid>("accountid"),
}
)
.Take(count);
var c = accounts.ToList().Count;
}
private static void QueryExpression()
{
// Get CRM Connection
orgService = new CrmConnection().GetConnection();
// Query using the paging cookie.
// Define the paging attributes.
// The number of records per page to retrieve.
int queryCount = 5000;
// Initialize the page number.
int pageNumber = 1;
//Create a column set.
//ColumnSet columns = new ColumnSet(true);
ColumnSet columns = new ColumnSet("name", "accountid");
// Create query expression.
QueryExpression query1 = new QueryExpression();
query1.ColumnSet = columns;
query1.EntityName = "account";
//query1.TopCount = count;
query1.AddOrder("accountid", OrderType.Ascending);
// Assign the pageinfo properties to the query expression.
query1.PageInfo = new PagingInfo();
query1.PageInfo.Count = queryCount;
query1.PageInfo.PageNumber = pageNumber;
query1.NoLock = true;
// The current paging cookie. When retrieving the first page,
// pagingCookie should be null.
query1.PageInfo.PagingCookie = null;
while (true)
{
if ((queryCount * pageNumber) > count)
{
break;
}
// Retrieve the page.
EntityCollection results = orgService.RetrieveMultiple(query1);
if (results.Entities != null)
{
var c = results.Entities.Count;
}
// Check for more records, if it returns true.
if (results.MoreRecords)
{
pageNumber++;
// Increment the page number to retrieve the next page.
query1.PageInfo.PageNumber = pageNumber;
// Set the paging cookie to the paging cookie returned from current results.
query1.PageInfo.PagingCookie = results.PagingCookie;
}
else
{
// If no more records are in the result nodes, exit the loop.
break;
}
}
}
private static void FetchParallel()
{
var fetch = @"<fetch count='5000' no-lock='true' page='{0}'>
<entity name='account'>
<attribute name='name'/>
<attribute name='accountid'/>
<order attribute='accountid'/>
</entity>
</fetch>";
//<all-attributes />
//<attribute name='name'/>
// <attribute name='accountid'/>
// <order attribute='accountid'/>
IDictionary<string, QueryBase> entityQuery = new Dictionary<string, QueryBase>();
entityQuery.Add("result", new FetchExpression(fetch));
// CreateOnlineOrganizationServiceUrl receives two params:
// ORG NAME - Name of your organization. If crm URL is https://tcardoso.crm6.dynamics.com it is TCARDOSO
// CRM REGION - Use the enumerator to choose the right one
var crmOrg = XrmServiceUriFactory.CreateOnlineOrganizationServiceUri("TCARDOSO", CrmOnlineRegion.AUSTRALIA);
var organisationSvcManager = new OrganizationServiceManager(crmOrg, "USER", "PASSWORD");
var queryResult = organisationSvcManager.ParallelProxy.RetrieveMultiple(entityQuery, true,
(pair, exception) => Console.WriteLine("{0} throwed {1}", pair.Key, exception.Message));
var c = queryResult.Values.FirstOrDefault().Entities.Count;
}
private static string CreateXml(string xml, string cookie, int page, int count)
{
StringReader stringReader = new StringReader(xml);
XmlTextReader reader = new XmlTextReader(stringReader);
// Load document
XmlDocument doc = new XmlDocument();
doc.Load(reader);
return CreateXml(doc, cookie, page, count);
}
private static string CreateXml(XmlDocument doc, string cookie, int page, int count)
{
XmlAttributeCollection attrs = doc.DocumentElement.Attributes;
if (cookie != null)
{
XmlAttribute pagingAttr = doc.CreateAttribute("paging-cookie");
pagingAttr.Value = cookie;
attrs.Append(pagingAttr);
}
XmlAttribute pageAttr = doc.CreateAttribute("page");
pageAttr.Value = System.Convert.ToString(page);
attrs.Append(pageAttr);
XmlAttribute countAttr = doc.CreateAttribute("count");
countAttr.Value = System.Convert.ToString(count);
attrs.Append(countAttr);
StringBuilder sb = new StringBuilder(1024);
StringWriter stringWriter = new StringWriter(sb);
XmlTextWriter writer = new XmlTextWriter(stringWriter);
doc.WriteTo(writer);
writer.Close();
return sb.ToString();
}
private static void Log(string querytype, StringBuilder text)
{
string path = @"C:\SOME_FOLDER\" + querytype + ".txt";
using (TextWriter tw = new StreamWriter(path, true))
{
// Add some information to the file.
tw.WriteLine(text.ToString());
}
}
}
}
Agora a classe de conexão (CrmConnection) com o CRM (a mesma que o SDK fornece):
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Tooling.Connector;
using System;
using System.Collections.Generic;
using System.Configuration;
namespace Performance.Retrieve
{
public class CrmConnection
{
IOrganizationService _orgService = null;
public IOrganizationService GetConnection()
{
return GetConn(null);
}
#region Private Methods
private IOrganizationService GetConn(string connectionString)
{
if (_orgService == null)
{
// Obtain connection configuration information for the Microsoft Dynamics
// CRM organization web service.
connectionString = GetServiceConfiguration();
if (connectionString != null)
{
// Connect to the CRM web service using a connection string.
CrmServiceClient conn = new CrmServiceClient(connectionString);
// Cast the proxy client to the IOrganizationService interface.
if (conn.OrganizationWebProxyClient != null)
{
_orgService = conn.OrganizationWebProxyClient;
}
else if (conn.OrganizationServiceProxy != null)
{
_orgService = conn.OrganizationServiceProxy;
}
else
{
if (ConfigurationManager.ConnectionStrings.Count > 2)
{
connectionString = ConfigurationManager.ConnectionStrings[2].ConnectionString;
// Connect to the CRM web service using a connection string.
conn = new CrmServiceClient(connectionString);
// Cast the proxy client to the IOrganizationService interface.
if (conn.OrganizationWebProxyClient != null)
{
_orgService = conn.OrganizationWebProxyClient;
}
else if (conn.OrganizationServiceProxy != null)
{
_orgService = conn.OrganizationServiceProxy;
}
}
}
//_orgService = (IOrganizationService)conn.OrganizationWebProxyClient != null ? (IOrganizationService)conn.OrganizationWebProxyClient : (IOrganizationService)conn.OrganizationServiceProxy;
}
}
return _orgService;
}
///
<summary>
/// Gets web service connection information from the app.config file.
/// If there is more than one available, the user is prompted to select
/// the desired connection configuration by name.
/// </summary>
/// <returns>A string containing web service connection configuration information.</returns>
private static string GetServiceConfiguration()
{
// Get available connection strings from app.config.
int count = ConfigurationManager.ConnectionStrings.Count;
// Create a filter list of connection strings so that we have a list of valid
// connection strings for Microsoft Dynamics CRM only.
List<KeyValuePair<string, string>> filteredConnectionStrings =
new List<KeyValuePair<string, string>>();
for (int a = 0; a < count; a++)
{
if (isValidConnectionString(ConfigurationManager.ConnectionStrings[a].ConnectionString))
filteredConnectionStrings.Add
(new KeyValuePair<string, string>
(ConfigurationManager.ConnectionStrings[a].Name,
ConfigurationManager.ConnectionStrings[a].ConnectionString));
}
// No valid connections strings found. Write out and error message.
if (filteredConnectionStrings.Count == 0)
{
Console.WriteLine("An app.config file containing at least one valid Microsoft Dynamics CRM " +
"connection string configuration must exist in the run-time folder.");
Console.WriteLine("\nThere are several commented out example connection strings in " +
"the provided app.config file. Uncomment one of them and modify the string according " +
"to your Microsoft Dynamics CRM installation. Then re-run the sample.");
return null;
}
return filteredConnectionStrings[0].Value;
}
///
<summary>
/// Verifies if a connection string is valid for Microsoft Dynamics CRM.
/// </summary>
/// <returns>True for a valid string, otherwise False.</returns>
private static Boolean isValidConnectionString(string connectionString)
{
// At a minimum, a connection string must contain one of these arguments.
if (connectionString.Contains("Url=") ||
connectionString.Contains("Server=") ||
connectionString.Contains("ServiceUri="))
return true;
return false;
}
#endregion Private Methods
}
}
Por fim o Web.Config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<appSettings>
<add key="queryType" value="FetchXml"/>
<!--<add key="queryType" value="LinqEarly"/>
<add key="queryType" value="LinqLate"/>
<add key="queryType" value="QueryExpression"/>
<add key="queryType" value="FetchXml"/>
<add key="queryType" value="FetchXmlParallel"/>-->
<add key="records" value="100140"/>
</appSettings>
<connectionStrings>
<add name="Server=CRM Online, organization=ORG_NAME, user=USER" connectionString="Url=URL; Username=USER; Password=PASSWORD; authtype=Office365" />
</connectionStrings>
</configuration>
Fique atendo as DLL’s que adicionei ao projeto, adicione as para a compilação!
Pronto!
Bom, por aqui termina este longo post. Espero ter ajudado com estes testes, a termos algum fundamento sobre a performance dos vários tipos diferentes de queries que podemos fazer no CRM. Tentei cobrir vários cenário de uso para que os resultados ajudem sua decisão.
[]’s,
Tiago
Power Platform by Tiago Michelini Cardoso 
Muito bom o POST! Parabéns!
CurtirCurtir