If you have been working on dynamics projects, I bet fetch xml is your best friend as it’s really an easiest way to query and execute in order to get required results. Along with that, constructing it in advanced find is super easy too.

Don’t forget about amazing XRM toolboxes which helps you creating even complex fetch xml queries.

It all sounds fun and easy till your fetch XML becomes really heavy with so many joins or you have a requirement where you need to make many joins.

A Quick tip!

You can have a maximum of 10 joins in fetch xml for dynamics CE.

You will notice that retrieval from dynamics is getting slower and slower as fetch xml becomes complicated. I have faced something similar multiple times. What I did?

well, there are multiple options one of which I want to cover today as using this option, there are no extra efforts.

latematerialize to the rescue!

All you got to do is add latematerialize=”true” in fetch xml properties like below and test is to see the magic.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" latematerialize="true">
  <entity name="account">
    <attribute name="emailaddress1" />
    <attribute name="statecode" />
    <attribute name="statuscode" />
    <attribute name="ownerid" />
    <attribute name="accountnumber" />
    <link-entity name="contact" from="contactid" to="primarycontactid">
      <attribute name="birthdate" />
      <attribute name="createdon" />
      <attribute name="contactid" />
      <attribute name="parentcustomerid" />
    </link-entity>
  </entity>
</fetch>

There are many internal technicalities as to how it works but to summarize, It breaks the data into multiple smaller segments which makes the retrieval of data faster unlike normal fetch xml which tries to query every at once.

if you are looking for official documentation on the same, please go here: Improve FetchXML request performance – Power Apps | Microsoft Docs