Stop Executing Queries Multiple Times

[pbiideas1]

I create a query in Power BI Desktop against a data source. Power BI Desktop insists on executing it multiple (usually three) times. This makes Desktop slow and for back-end data sources exerts additional load that can also incur additional costs. Note: I am not talking about the scenario where a query is referred to multiple times in M, rather just a simple query loaded into one table in the data model. I assume the engine is checking data types, etc - but the current approach is very slow/painful. Optimisation is needed!

[Bob_Hornung]

A decent article/vid on this behavior can be found here: https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/ I think the extra querying of metadata is the most difficult to resolve. The workaround in the vid of manually specifying data structure in M code isn't realistic for most people.

[jered_little]

REST API calls are sending almost 10 queries for datasets larger than 10k records before loading. (PBI Server desktop Sept. 2019 version)

[rsmithman]

How is this still not resolved?

[srikrishnan_ram]

How is this not addressed yet. We are facing lot of performance issues querying Hive with billions of rows. The power bi file has typically multiple tables used and each of the table executes 3 queries internally.

[harredondo1]

This is unacceptable, running queries multiple times is degradding our server performance. MS should fix this MAJOR ISSUE.

[sally_levesque]

Please see this help post for details of the problems this is causing for users: https://community.powerbi.com/t5/Power-Query/Power-BI-runs-query-multiple-times-on-backend/td-p/197745

[n0s01ls]

Power BI load size is doubled due to running query 2 times while pulling data from Azure blob storage.

[fbcideas_migusr]

It's now 2024 September and problem still exists. (in my opinion this is not an idea, it must be descripted as an issue)

I'm in PBI desktop and have a simple code to test the issue

let

  Source = Sql.Database(".", "TEST", [Query="select * from [dbo].[ProductSold]"]),

  selectARow = Table.SelectRows(Source, each [ProductID] = 3 ),

  selectAColumn = Table.SelectColumns(selectARow,{"ProductID"})[ProductID]{0},

  testProc = Sql.Database(".", "TEST", [Query="exec test2 " & Text.From(selectAColumn)])

in

  testProc

In this situation "select * from [dbo].[ProductSold]" query runs only once but the "exec test2 " query runs twice.

[ishalev]

I am joining to this idea with my idea of improving Odbc.Query function to trigger only one query instead of two.

https://community.fabric.microsoft.com/t5/Power-Query/Odbc-Query-triggering-only-one-query/m-p/4385658