Skip to main content

Power BI

Needs Votes

Stop Executing Queries Multiple Times

Vote (169) Share
's profile image

on 01 Feb 2019 23:43:45

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!

Comments (18)
's profile image Profile Picture

Uğur Demir on 03 Sep 2024 13:46:17

RE: Stop Executing Queries Multiple Times

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 issuelet  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  testProcIn this situation "select * from [dbo].[ProductSold]" query runs only once but the "exec test2 " query runs twice.

's profile image Profile Picture

Nowreena Shakdwipee on 18 Feb 2021 19:07:58

RE: Stop Executing Queries Multiple Times

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

's profile image Profile Picture

Marzipanly Runner on 21 Sep 2020 11:52:18

RE: Stop Executing Queries Multiple Times

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

's profile image Profile Picture

Power BI User on 10 Jul 2020 17:20:40

RE: Stop Executing Queries Multiple Times

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

's profile image Profile Picture

SK on 06 Jul 2020 00:16:10

RE: Stop Executing Queries Multiple Times

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.

's profile image Profile Picture

Ryan Smithman on 06 Jul 2020 00:12:46

RE: Stop Executing Queries Multiple Times

How is this still not resolved?

's profile image Profile Picture

Jered on 06 Jul 2020 00:11:09

RE: Stop Executing Queries Multiple Times

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

's profile image Profile Picture

Bob Hornung on 06 Jul 2020 00:04:18

RE: Stop Executing Queries Multiple Times

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.

's profile image Profile Picture

Bob Hornung on 06 Jul 2020 00:04:06

RE: Stop Executing Queries Multiple Times

This is becoming a significant concern for us as well. Power BI seems to be putting an unnecessary load on our source systems.

's profile image Profile Picture

Power BI User on 05 Jul 2020 23:52:49

RE: Stop Executing Queries Multiple Times

What type of source are you having issues with? I was seeing the behaviour you describe with Excel files on SharePoint and found that defining them as "From Web" rather than "From Excel" means that they are cached and not reread