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)
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.
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.
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
RE: Stop Executing Queries Multiple Times
This is unacceptable, running queries multiple times is degradding our server performance.
MS should fix this MAJOR ISSUE.
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.
RE: Stop Executing Queries Multiple Times
How is this still not resolved?
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)
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.
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.
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