API calls and parallel loading

[SanderVeeken]

Hey everyone,

I ran into an issue and I thought I'd share it here, I'd appreciate thoughts, solutions or shared experiences although it is "sort of" solved for now.

Basically I have three API calls in PowerQuery that I use to build various tables. The info is combined into multiple tables but I only ever refer to the same three source queries (they're not duplicated but referenced).

It turns out that when parallel loading is on (either globally or for that file specifically) the API is queried way more than three times. Sometimes a single call will occur as many as four times in quick succession. As this is a very small server and the queries are relatively large, this eventually generates a 500 error (internal server error) as the server can't process all of that at the same time. When parallel loading is off, each call is only made once (so three calls in total).

I was under the impression that parallel loading of tables was meant to load things quicker by running parallel processes when possible. I don't see how running the same API call 4 times could be quicker than running it once and processing it four times over different CPU cores (which is what I assumed was happening). Either you have the data or you don't, and querying multiple times can only be slower unless you actually split the data in parts like a download manager would do - but that isn't happening here.

Is this normal behavior? Has anyone had a similar experience? What is the underlying logic here?

[lbendlin]

The situation is even worse. While you can prohibit parallel loading on the desktop you cannot do that on the service.  The service will always run stuff in parallel.

Your data source should have a cache in place from which it can serve these repetitive requests.

[SanderVeeken]

But that doesn't seem to be the case. The server logs show that the auto refresh from the service only runs three API queries as expected. 

[v-nuoc-msft]

Hi @SanderVeeken 

When parallel loading is enabled, it is not uncommon for Power Query to make multiple calls to the same API.

If you have multiple queries that reference the same source query, Power query might try to refresh each query in parallel, causing it to make multiple calls to the API at the same time.

This is because Power Query does not always recognize that the referenced queries are extracted from the same API call, and therefore treats them as separate data sources that can be loaded in parallel.

You can try the following steps to alleviate this problem:

Disable parallel loading: As you can see, turning off parallel loading prevents multiple simultaneous API calls.

Composite query: If possible, try to combine API calls into a single query and then reference the combined query in various tables. This way, Power Query will only need to make one API call.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SanderVeeken]

The thing is that I'm already referencing the source queries. There are three separate API calls that can't be combined (one is "students", another is "courses", another is "teachers"). I get current season info from those API calls. I get previous season history from excel dumps on SharePoint. I append them to get a complete picture. I would have thought that using references prevents PQ from doing multiple requests to the same endpoint but apparently not.

[lbendlin]

You can try Binary.Buffer but even that does not shield reliably against the meta data chatter.