CRM Crate

How to improve performance of FetchXML request in Power Apps?

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 “support@crmcrate.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' value='support@crmcrate.com' />   
          </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 retrieval

The 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 data

The 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 LateMaterialize option 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 LateMaterialize  option will perform as given below.
  1. Pull only the primary ID of the top number of records specified.
  2. 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 LateMaterialize option in your FetchXML query.

Use Case 1

Use the LateMaterialize option when your table or entity contains many columns.

Use Case 2

Use the LateMaterialize option when your table or entity has multiple link to other tables for column data.

Use Case 3

Use the LateMaterialize option when your table or entity contains logical columns

How to use LateMaterialize option in Fetch XML query?

  • To use LateMaterialize option, 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 LateMaterialize option.
<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>

Late materialization might not always provide a performance benefit. It’s best used if you’re experiencing performance issues with your existing fetch request.

An Important Note

Thus, we learned to improve performance of FetchXML request in Power Apps.


5 1 vote
Article Rating
Subscribe
Notify of
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
UNISDA SELALU DI DEPAN
4 months ago

Good post! We will be linking to this particularly great post on our site. Keep up the great writing

error: CRM Crate Security Engine - Disabled Right Click & Selection!

Congratulations!

Well Done,
Welcome to CRM Crate

Stay tuned with us and get all latest updates and learning in Microsoft CRM and related techonologes.