Overview:
We will discuss the common issue
that we usually face in our projects. We have slowness of the application, and
most of the time, it's identified that we had a FetchXml request that we are
using to sync with the Microsoft Dataverse.
Introduction:
When working on dynamic projects,
we usually use an easy way to query and execute the FetchXml to get the
results. For the construction of the FetchXml, we mostly used Advance Find, and
also we can use the Xrm Toolbox for building a FetchXml.
The most common issue we faced was
the slowness of the FetchXml query as we moved toward the complexity. There is
a way that we can use to resolve this issue.
How to improve the slowness:
The traditional fetch will pull
all the columns for the top table records given in the filter criteria. For
example, we query to pull 500 records from the table containing 100 columns and
100000 approx. Rows that meet the filter criteria. This case will give us the
following issues.
·
To
return the result set of 500 records, it pulls almost 99500 rows with all
columns and then returns 500 rows.
·
Optimizer
for the query can generate an arbitrary order when using the child columns for
the retravel but result in a data order we don't want to use.
We can use the LateMaterialize
option in the FetchXml request to resolve the slowness issue. It will break the
request into smaller, usable segments, improving the performance of
long-executing FetchXml requests. The improvement mostly depends on the data
distribution for each table and link table used.
After using the LateMaterialize,
the created fetch will.
·
Only
pulling the primary ID of the top number of records given in the query also
fulfilling the filter criteria.
·
Retrieved
on the needed data column based on the primary IDs given on the filter
criteria. Like if six columns are needed in the given query. It will on
retrieving them.
Example for using LateMaterialize
in FetchXml:
|
<fetch
version="1.0" output-format="xml-platform"
latematerialize="true" mapping="logical"
distinct="true"> <entity
name="[entity]"> <attribute
name="[attribute]" /> <link-entity
name="[entity]" from="[linked entity]" to="[linked
entityid]" link-type="outer" alias="[alias]"> <attribute name="[name
of linked entity column]" /> </link-entity> <filter type=[filter
type]> <condition
attribute="[column]" operator="[operator]"
value="[value]"/> </filter> </entity> </fetch> |
Above is the sample we can use to
modify our FetchXml query to resolve the optimization issue.
|
<fetch
version="1.0" output-format="xml-platform"
latematerialize="true" mapping="logical" distinct="true"> <entity
name="account"> <attribute
name="accountnumber" /> <attribute
name="createdby" /> <attribute
name="ownerid" /> <link-entity
name="account" from="accountid"
to="parentaccountid" link-type="outer"
alias="oaccount"> <attribute
name="createdby" /> <link-entity
name="account" from="accountid" to="accountid"
link-type="outer" alias="oaccount1"> <attribute
name="createdby" /> <attribute
name="accountid" /> <attribute
name="name" /> </link-entity> </link-entity> <link-entity
name="account" from="accountid" to="accountid"
link-type="outer" alias="oaccount2"/> <filter type='and'> <condition
attribute="statecode" operator="eq"
value="2"/> </filter> </entity> </fetch> |
So, with the above FetchXml, we
are almost retrieving the account hierarchy. As a developer, we know that if we
use the self-referential relationship, it will affect the performance of the
project badly. Still, by using LateMaterialize, we can resolve that
issue.
LateMaterialize is the most beneficial.
·
Query
where there are one or more linked conditions to other tables, and their
columns are used for the data.
·
Query
where there are tables that have many columns and logical
columns.
I hope this will be helpful.
Thanks.
Conclusion
So, in this blog, we give you an option that can be utilized
to resolve the performance-related issue faced while using FetchXml in the
project.
No comments:
Post a Comment