We will learn to improve performance of FetchXML request in Power Apps. Before we start, make sure to subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.
What is FetchXML?
FetchXML is a proprietary XML based query language of Microsoft Dataverse which is used to query data using either the Web API or the Organization service. FetchXML is based on a schema that describes the capabilities of the language and it supports similar query capabilities as QueryExpression.
Example of FetchXML query
In the following example, the FetchXML query retrieve’s all rows / records from a table called “CRM Crate Subscribers”.
<fetch mapping='logical'> <entity name='crmcratesubscriber'> <attribute name='crmcrateid'/> <attribute name='name'/> </entity> </fetch>
In the following example, the FetchXML query retrieve’s rows / records from a table called “CRM Crate Subscribers” where the email address of the owning user is equal to “email@example.com”.
<fetch mapping='logical'> <entity name='crmcratesubscriber'> <attribute name='crmcrateid'/> <attribute name='name'/> <link-entity name='systemuser' to='owninguser'> <filter type='and'> <condition attribute='emailaddress' operator='eq' firstname.lastname@example.org' /> </filter> </link-entity> </entity> </fetch>
How to improve FetchXML request performance?
- Operating a traditional FetchXML query for a given number of the top table records will pull all the fields / columns in the select list that satisfies the filter criteria.
- Consider that you’re pulling the top 200 records on a table that has 50 columns and 100,000 records / rows that meet the filter criteria. Two specific problems can arise on execution of this FetchXML query.
Issue 1 – Unnecessarydata retrievalThe 99,800 records / rows will pull all columns or fields, even though you only need to populate the select list for 200 rows when returning to the client.
Issue 2 – Disruption in order of dataThe query optimizer can produce an arbitrary order when retrieving the child columns which eventually results in a data order you don’t want.
Using LateMaterialize option to improve performance
- Performance improvements completely depends upon the data distribution & segregation for each operating table and linked table.
- We can use the
LateMaterializeoption in FetchXML query requests to break up & segregate such requests into smaller chunks & more usable segments, which can improve the performance of long-running FetchXML requests.
- The FetchXML request with
LateMaterializeoption will perform as given below.
- Pull only the primary ID of the top number of records specified.
- Select only the columns / fields of data needed based on the primary IDs that were retrieved.
- Thus, by retrieving only the needed data after the primary IDs are collected, the retrieval is much faster because data that’s not required for the current operation is excluded from the request’s operation.
- Below are few use cases when you can apply the
LateMaterializeoption in your FetchXML query.
Use Case 1Use the
LateMaterializeoption when your table or entity contains many columns.
Use Case 2Use the
LateMaterializeoption when your table or entity has multiple link to other tables for column data.
Use Case 3Use the
LateMaterializeoption when your table or entity contains logical columns
How to use
LateMaterialize option in Fetch XML query?
- To use
LateMaterializeoption, you need to add a new tag (latematerialize=”true”) in your FetchXML query.
<fetch version="1.0" output-format="xml-platform" latematerialize="true" mapping="logical" distinct="true">
- Below pseudo code describes the FetchXML query with
<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>