Dataverse query performance question

[mbowler]

Hi,

Can someone explain why the two following two queries to our Dynamics system are dramatically different performancewise (ie ~20 sec vs 5mins+). They both produce exactly the same table:

A.

Source = CommonDataService.Database("mycomp.dynamics.com"),

dbo_my_entity = Source{[Schema="dbo",Item="my_entity"]}[Data]

B.

Source = CommonDataService.Database("mycomp.dynamics.com"),

dbo_my_entity = Value.NativeQuery(Source, "SELECT * FROM my_entity", null, [EnableFolding=true])

A. Takes over 5 mins for two such queries of different entity tables. B. takes about 20 sec for the same. The problem I have is that A. is the default PQ code used if you select Dataverse as your data source on a new query, so my developers are using this and it's creating havoc on refreshing previews etc.

[Poojara_D12 · Accepted solution]

Hi @mbowler 

The performance difference between the two Power Query approaches stems from how query folding and data retrieval are handled when connecting to Dataverse (Dynamics). In method A, Power Query uses the default connector path — CommonDataService.Database("mycomp.dynamics.com") — which retrieves metadata and applies transformations through the OData API layer. This process results in Power Query downloading the full dataset into memory before applying filters or transformations locally, leading to very slow performance, especially for large entities. In contrast, method B uses Value.NativeQuery, which sends a direct SQL-like query (SELECT * FROM my_entity) to the Dataverse endpoint via the TDS (Tabular Data Stream) interface. This means the filtering and data shaping happen server-side, and only the processed results are sent back to Power Query, allowing for significant speed improvements — often seconds instead of minutes. Essentially, method B leverages query folding and server execution, while method A relies on a less efficient OData fetch. The default behavior of Power Query when choosing “Dataverse” as a source still uses the OData path, which prioritizes compatibility and schema safety but sacrifices performance. For large datasets or production refreshes, using Value.NativeQuery with the TDS endpoint is therefore the preferred approach.

[v-hjannapu]

Hi @mbowler,

I would also take a moment to thank @MasonMA , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Communitu Support Team.

[v-hjannapu]

Hi @mbowler,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support team.

[MasonMA]

Interesting topic! Regarding why Query A is extremely slow, if you look at this article from Microsoft (it also explained Query B)

https://learn.microsoft.com/en-us/power-bi/guidance/powerbi-modeling-guidance-for-power-platform 

"If you're using the Dataverse connector (formerly known as the Common Data Service), you can add the CreateNavigationProperties=false option to speed up the evaluation stage of a data import.

The evaluation stage of a data import iterates through the metadata of its source to determine all possible table relationships. That metadata can be extensive, especially for Dataverse. By adding this option to the query, you're letting Power Query know that you don't intend to use those relationships. The option allows Power BI Desktop to skip that stage of the refresh and move on to retrieving the data."

So Each [Data] access (that Source{[Schema="dbo",Item="my_entity"]}[Data]) doesn’t just send a single SQL query.

It first makes multiple metadata calls (schema, relationship, permissions, type info) to Dataverse. Then it requests the data in paged batches (often thousands records per call). These metadata and pagination calls happen sequentially and latency adds up.

[Cookistador]

Query B is forcing the query folding

Query folding will translate all transformation in SQL command, it is the best way to retrieve data from data sources 

You can find more info under the following link: https://learn.microsoft.com/en-us/power-query/query-folding-basics

You can always switch the datasource if your are not using too much semantic model with the query A