FetchXML takes so long to load data on website that it crashes Power Pages services

(1) Share

Report

Posted on by ChompingBot

Hello ! I have a FetchXML query through which I load in an HTML table about 500 or more records. The query takes so long to run that apparently it times out a service called Azure Front Door. Is there any way to query faster ? I have tried on the UI side to include pagination so the DOM won't have to load all the table rows at once but that did not fix it.

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Michael E. Gernaey 53,963 Moderator on at

Like (0)

Report
Copy link

Link copied!

HI @ChompingBot

Sounds like the query itself is just expanding too much and taking its time.

Are smaller FetchXMLs working ok and its just this one?

Can you explain what its fetching and maybe share your FetchXML for review?

Was this reply helpful? Yes No
Suggested answer

wolenberg_ 1,476 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hello,

Yes — this is a known performance bottleneck when using FetchXML in Power Pages to load large datasets directly into the DOM. If the query takes too long, it can trigger a 504 Gateway Timeout from Azure Front Door, which acts as a reverse proxy and load balancer.

According to Microsoft’s FetchXML optimization guide, queries with many joins or computed fields can be slow.

Try to simplify your query structure.

Another Tip:

Use browser dev tools or HAR logs to measure how long the FetchXML call takes. If it consistently exceeds 2–5 seconds, consider breaking the query into smaller chunks or offloading it to a background process.

Was this reply helpful? Yes No
ChompingBot 12 on at

Like (0)

Report
Copy link

Link copied!

Hi ! Thank you for your replies @wolenberg_ and @Michael E. Gernaey. Suprisingly pages that contain more complex FetchXML queries load relatively okay but that's due to the query being filtered so less items get loaded in (about a 100). In this case for this page is just loading everything from the primary entity and contains about 14 different attributes. Upon searching the internet I came upon FetchXML pagination at this [website](https://wilmeralcivar.com/tagfiltering_searchbar_pagination_powerpages/). Would something like this work to offload a bit the load by loading the data incrementally and is it still relevant (since the post is a bit old)?

Was this reply helpful? Yes No
Michael E. Gernaey 53,963 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @ChompingBot

Logistically Dataverse does in fact support Pagination. So what you want to do is possible. I am still... concerned by the volume of data and also, are you limiting the # of columns coming back?

Was this reply helpful? Yes No
Ajlan 237 on at

Like (0)

Report
Copy link

Link copied!

@ChompingBot Hello,

If possible could you please paste your Fetchxml for review?

Thanks

Was this reply helpful? Yes No
ChompingBot 12 on at

Like (0)

Report
Copy link

Link copied!
Yup sure @Ajlan, I have put the FetchXML context below:

{% fetchxml products %} <fetch version="1.0" mapping="logical" distinct="true"> <entity name="product"> <attribute name="attributeString" /> <attribute name="attributeString" /> <attribute name="attributeString" /> <attribute name="attributeLookup" /> <attribute name="attributeLookup" /> <attribute name="attributeLookup" /> <attribute name="attributeLookup" /> <attribute name="attributeFormula(calculated)" /> <attribute name="attributeFloat" /> <attribute name="attributeFloat" /> <attribute name="attributeFloat" /> <attribute name="attributeFloat" /> <attribute name="attributeChoice" /> <attribute name="attributeLookup" /> <attribute name="attributeLookup" /> <link-entity name="entityB" from="entityA_Id" to="entityB_Id" link-type="inner" alias="entityB"> <attribute name="entityA_Id" alias="eBId" /> <attribute name="entityA_Name" alias="eBName" /> </link-entity> <link-entity name="entity_C" from="entityD_Id" to="entityC_Id" link-type="inner" alias="entityC"> <attribute name="entityC_Id" alias="eCId" /> <attribute name="entityC_name" alias="eCName" /> </link-entity> <link-entity name="entity_D" from="entityD_Id" to="entityE_Id" link-type="inner" alias="entityD"> <attribute name="entityD_Id" alias="eDId" /> <attribute name="entityD_name" alias="eDName" /> </link-entity> <link-entity name="entity_E" from="entityD_Id" to="entityE_Id" link-type="inner" alias="entityE"> <attribute name="entityD_Id" alias="eDId" /> <attribute name="entityD_name" alias="eDName" /> </link-entity> </fetch> {% endfetchxml %}

Was this reply helpful? Yes No
Suggested answer

Fubar 8,503 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

A) I would run the query from XrmToolBox to get an idea of how long it takes to execute (without the porta in the picture)

B) I am not sure that the query will give you the outcome you desire or not.

As they are all Inner joins, you are basically saying join on Products to the first table, then join that result with the 2nd table, then join that result on the 3rd table etc etc. If it is the desired result, re-ordering the joins could improve performance if there will be a greater number of 'matches' on particular tables.

Rather than using the default value you can make use of Count to limit the returned values and then paging / paging cookie to get the next records - however I suspect it is the query itself that slow and limiting may only have a small impact.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/page-results?tabs=sdk

Was this reply helpful? Yes No
Michael E. Gernaey 53,963 Moderator on at

Like (0)

Report
Copy link

Link copied!

HI @ChompingBot

So to come back to this after reviewing your FetchXML.

Dataverse is not great when you are using so many joins. I mean SQL itself won't be either really as you do not personally have easy control over indexing etc your Tables (as if it was your own straight sql).

1) I do not see any type of filtering, so you are simply Table scanning everything, which is bad and super slow. Is there any opportunity to limit how many rows you really need to join on?

2) You could add the Page attribution on the top and set it to anything up to 5000, but that doesn't do much if you cannot even return (in memory) that many due to all the joins.

Would it be ok to ask what you are trying to achieve so we can recommend (possibly) a different way to do it?

The first thing I would look at is creating views and leverage those, versus running this fetchxml by itself.

Was this reply helpful? Yes No