In Dynamic CRM and data verse, we use a lot of tools to design and develop fetch XML query expressions to retrieve the records from multiple interconnected tables through primary keys (relationships).
At times we use the fetchxml builder plugin in XrmTool Box to get connected to the Data Verse environment and pull out the records based upon the complex business requirements utilizing inner and outer joins along with complex filter criteria.
There have been various multiple advantages of using Fetch XML rather than using other query languages out in there including LINQ as Fetchxml supports inner joins, and aggregations which are not supported in query expressions.
Now at times what exactly happens is, let's say you want to pull out 500 calls from a table of 100,000 records with 100 plus columns, and based upon your filter criteria it takes a couple of minutes to fetch the records.
The reason stated application traditionally fetchxml first fetches all the 100,000 records with hundred columns. And then it performs a filter based upon your query to fetch in the record.
To overcome the challenge what if we design a query that first pulls out the primary id of the required 500 records followed by required columns to return to the client. To achieve the same Microsoft introduced the term “latematerialize”.
By using this term latematerialize in your fetch XML, first, it will put only the primary IDs of those 500 records which meet the filter criteria. Once the primary ID has been retrieved then it will consider only those columns to be returned to the client which has been mentioned in the query.
Pulling only the required columns with required data wherein the criteria has been meeting makes it much faster for the end users to get the data
![Improving the Fetch XML performance in Dynamics CRM.]()